Remove jsonb array element by value

simpleman picture simpleman · Oct 27, 2016 · Viewed 10.9k times · Source

I did figure out how to remove a value from an array for a single record, but how to do it for many of them. The problem is in the way how I use the subquery. As it has to return only single element. Maybe my approach is wrong.


    Given input: '{attributes:['is_new', 'is_old']}'
    Expected result '{attributes: ['is_old']}' #remove 'is_new' from jsonb array


    Real example:
    #   sku  |           properties 
    # -------+--------------------------------
    #  nu3_1 | {                             +
    #        |     "name": "silly_hodgkin",  +
    #        |     "type": "food",           +
    #        |     "attributes": [           +
    #        |         "is_gluten_free",     +
    #        |         "is_lactose_free",    +
    #        |         "is_new"              +
    #        |     ]                         +
    #        | }  


#Query that removes single array element:

SELECT c.sku, jsonb_agg(el) FROM
catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku where el  'is_new'
GROUP BY c.sku;

#Update query that removes single array element in single record

UPDATE catalog SET properties=jsonb_set(properties, '{attributes}', (
    SELECT jsonb_agg(el) FROM
    catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku
    WHERE el  'is_new' AND c.sku='nu3_1'
    GROUP BY c.sku
    )
) WHERE sku='nu3_1';

The question again is. How to remove jsonb array element by value for many database records?

Answer

klin picture klin · Nov 1, 2016

Use jsonb_set() and the delete operator -:

update catalog
set properties = 
    jsonb_set(properties, '{attributes}', (properties->'attributes') - 'is_new');

Test it in db<>fiddle.