arrays - 按值删除 jsonb 数组元素

Question

我确实弄清楚了如何从数组中删除单个记录的值，但如何为其中的许多记录执行此操作。问题在于我如何使用子查询。因为它必须只返回单个元素。也许我的方法是错误的。

    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) FROMcatalog 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 recordUPDATE 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 数组元素？

Answer 1

使用jsonb_set() and the delete operator - :

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

在 db<>fiddle. 中测试它