我确实弄清楚如何从一个数组中删除单个记录的值,但是如何为其中许多记录删除它.问题在于我如何使用子查询.因为它必须只返回单个元素.也许我的方法是错的.
- 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';
解决方法
使用
jsonb_set() and the delete operator -：
- update catalog
- set properties =
- jsonb_set(properties,(properties->'attributes') - 'is_new');
