根据类型更新JSONB作用域数组中的所有元素

针对你的JSONB字段更新需求，可以使用PostgreSQL的JSON函数组合实现：

UPDATE your_table
SET payload = (
  SELECT jsonb_agg(
    jsonb_set(item, '{edits}', (
      SELECT jsonb_agg(
        CASE edit->>'type'
          WHEN 'A' THEN edit || jsonb_build_object('value', jsonb_build_object('src', edit->'value'))
          WHEN 'B' THEN edit || jsonb_build_object('value', jsonb_build_object('url', edit->'value'))
          ELSE edit -- 保留未定义type的条目
        END
      )
      FROM jsonb_array_elements(item->'edits') AS edit
    ))
  )
  FROM jsonb_array_elements(payload) AS item
)
WHERE payload IS NOT NULL; -- 跳过payload为空的行

语句说明： #

• jsonb_array_elements(payload)：将顶层payload数组拆分为单个元素（每个元素包含edits数组）
• 内层jsonb_array_elements(item->'edits')：拆分每个元素中的edits数组，得到单个编辑对象
• CASE分支：根据type字段生成新的value对象，用||运算符合并到原对象中（覆盖原value）
• jsonb_agg：将处理后的单个编辑对象重新聚合成数组
• jsonb_set：将更新后的edits数组替换回原item对象
• 外层jsonb_agg：将所有处理后的item重新聚合成新的payload数组

执行这条语句后，数据会完全符合预期结果，空edits数组的条目会被原样保留。

内容的提问来源于stack exchange，提问作者Martin