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?
Use jsonb_set()
and the delete operator -
:
update catalog
set properties =
jsonb_set(properties, '{attributes}', (properties->'attributes') - 'is_new');
Test it in db<>fiddle.