如何更新嵌套JSON数组中指定qId字段的数值？

原SQL存在两个关键问题：

• 路径错误：{ques,qId}未考虑顶层是数组、ques本身也是数组的嵌套结构，无法直接定位目标qId节点
• 筛选条件错误：COLUMN_NAME->>'qId'无法从顶层数组中提取qId值，因为qId嵌套在两层数组内部

全量更新所有符合条件的节点 #

以下SQL会遍历顶层数组的每个对象，再遍历每个对象的ques数组，将所有qId=100的节点替换为101：

UPDATE TABLE_NAME
SET COLUMN_NAME = (
  SELECT jsonb_agg(
    jsonb_set(
      obj,
      '{ques}',
      (
        SELECT jsonb_agg(
          CASE WHEN elem->>'qId' = '100' THEN jsonb_set(elem, '{qId}', '101'::jsonb) ELSE elem END
        )
        FROM jsonb_array_elements(obj->'ques') AS elem
      )
    )
  )
  FROM jsonb_array_elements(COLUMN_NAME) AS obj
);

仅更新包含目标节点的行 #

如果要避免对无qId=100的行做无效更新，可以添加WHERE条件：

UPDATE TABLE_NAME
SET COLUMN_NAME = (
  SELECT jsonb_agg(
    jsonb_set(
      obj,
      '{ques}',
      (
        SELECT jsonb_agg(
          CASE WHEN elem->>'qId' = '100' THEN jsonb_set(elem, '{qId}', '101'::jsonb) ELSE elem END
        )
        FROM jsonb_array_elements(obj->'ques') AS elem
      )
    )
  )
  FROM jsonb_array_elements(COLUMN_NAME) AS obj
)
WHERE COLUMN_NAME @> '[{"ques": [{"qId": 100}]}]'::jsonb;

逻辑说明 #

1. 用jsonb_array_elements拆分顶层JSON数组为单个对象obj
2. 对每个obj的ques数组再次拆分，遍历每个元素elem
3. 通过CASE判断，将qId=100的元素替换为qId=101，其余元素保留原样
4. 用jsonb_agg将处理后的元素重新组合为数组，替换回原对象的ques字段
5. 最后将所有处理后的对象重新组合为顶层数组，更新回目标字段

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