删除jsonb数组中的某个元素

Question

我已经弄清楚如何从数组中移除单个记录的值，但如何对多个记录进行操作呢？问题出在我的子查询使用方式上。因为它必须只返回单个元素。也许我的方法是错误的。

给定输入：'{attributes:['is_new', 'is_old']}' 期望结果 '{attributes: ['is_old']}' ＃从jsonb数组中删除'is_new'

真实示例： # sku | properties # ------- + -------------------------------- # nu3_1 | {+ # | "name": "silly_hodgkin",+ # | "type": "food",+ # | "attributes": [+ # | "is_gluten_free",+ # | "is_lactose_free",+ # | "is_new"+ # | ]+ # | }

删除单个数组元素的查询语句：

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 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数组中删除多个数据库记录？

- simpleman

Answer 1

使用jsonb_set()和删除运算符-：

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

在db<>fiddle中进行测试。

- klin

---

在我提问之前，我确实尝试过'-'运算符，但它没有起作用。使它起作用的是括号（属性->'attributes'）。如果没有它们，它就不起作用。我不明白。有什么区别吗？现在我明白了'->'和'-'都是运算符。看起来'-'具有更高的优先级，并尝试将其用于两个字符串，这是行不通的。 谢谢@klin。 - simpleman

能否在这里使用where语句？比如说，如果你有一个包含ID属性的对象数组，能否使用where ID = 1来筛选数据？ - N P

@NickPocock - 是的，你可以像在任何其他UPDATE语句中一样添加where子句，请参见此示例。 - klin

像这样的东西怎么样？http://stackoverflow.com/questions/42299061/removing-element-from-array-within-object-jsonb非常苦恼！ - N P

嗨，@klin，如果我有一个名为 parents 的字段，其内容为 [123, 345, 789]，我是否可以使用 jsonb_set 删除其中的一个元素，例如 123？还有其他解决方案吗？ - Brook

Answer 2

所以，我相信你要找的QRY是：

with q as (
  select distinct sku, jsonb_set(properties,'{attributes}',jsonb_agg(el) over (partition by sku),false) new_properties
  from (
    select 
      sku, jsonb_array_elements_text(properties->'attributes') as el, properties
    from catalog
  ) p
  where el != 'is_new'
)
update catalog set properties = q.new_properties from q where catalog.sku = q.sku
;

请注意，我假设你的sku至少为UK！

- Vao Tsun

---

我刚刚重写了查询语句，以便它能正常工作。我个人绝对更喜欢“-”运算符。 - Vao Tsun

Answer 3

你的解决方案可行，而且你还向我介绍了一些新东西，比如窗口函数，给了我替代方案的思路：

WITH q as (
  SELECT c.sku as sku,  jsonb_set(properties, '{attributes}', jsonb_agg(el)) as new_properties
    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 catalog SET properties=q.new_properties FROM q WHERE catalog.sku=q.sku;

- simpleman

Answer 4

根据文档：

json_build_object ( VARIADIC "any" ) → json

jsonb_build_object ( VARIADIC "any" ) → jsonb

使用可变参数列表构建JSON对象。按照惯例，参数列表由键和值交替组成。键参数被强制转换为文本；值参数根据to_json或to_jsonb进行转换。

json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

update catalog
set properties = properties - 'attributes' || jsonb_build_object('attributes','[is_gluten_free,is_lactose_free,is_new]')
where properties ? 'attributes' 
returning *;

现在你可以替换键，也可以替换值。只需确保attributes作为顶级键。

- jian