PostgreSQL中正确更新JSON对象数组指定字段的方法

场景与需求 #

在PostgreSQL表t中存储了如下JSON格式的数据：

{"jobTitle":"Technical Writer","distance":60,"skills":[{"name":"Bootstrap (Front-End Framework)","isPrimary":true,"type":""}, {"name":"Javascript","isPrimary":false,"type":""}]}

需要更新skills数组内每个对象的isPrimary字段：

• 若isPrimary为true，则改为数值5
• 若isPrimary为false，则改为数值1

原错误查询及问题 #

编写的初始查询语句如下：

UPDATE t
SET body = jsonb_set(body::jsonb, '{skills}', s.skills_updated::jsonb)
FROM (
  SELECT
  jsonb_agg(
     jsonb_set(skills::jsonb, '{isPrimary}', 
        CASE
            WHEN skills ->> 'isPrimary' = 'true' THEN '5'::jsonb
            ELSE '1'::jsonb
        END
    )
  ) as skills_updated
  FROM t,
    json_array_elements(body -> 'skills') as skills
) s;

执行后发现所有记录的skills数组都合并了表中所有记录的技能项，而非仅更新自身的数组。

问题原因 #

原查询的子查询未按单条记录分组，而是将表中所有记录的skills元素全部聚合为一个数组，导致每条更新的记录都使用了这个全局聚合的结果。

修正后的查询 #

假设表t有主键id，修正后的查询如下：

UPDATE t
SET body = jsonb_set(body::jsonb, '{skills}', s.skills_updated)
FROM (
  SELECT
    t.id,
    jsonb_agg(
      jsonb_set(skill::jsonb, '{isPrimary}', 
        CASE WHEN (skill ->> 'isPrimary') = 'true' THEN '5'::jsonb ELSE '1'::jsonb END
      )
    ) AS skills_updated
  FROM t,
    jsonb_array_elements(body::jsonb -> 'skills') AS skill
  GROUP BY t.id
) s
WHERE t.id = s.id;

逻辑说明 #

1. 子查询中通过jsonb_array_elements将每条记录的skills数组拆分为单个元素
2. 按记录主键id分组，对每个元素的isPrimary字段进行替换
3. 用jsonb_agg重新聚合为该记录专属的更新后skills数组
4. 通过主键关联，将更新后的数组写回原记录的body字段

预期结果 #

执行修正后的查询后，每条记录的skills数组仅更新自身元素，结果如下：

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Node.js" },{ "isPrimary": 1, "name": "Python" },{ "isPrimary": 1, "name": "Javascript" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Mysql" },{ "isPrimary": 1, "name": "Nest.js" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Postgres" },{ "isPrimary": 1, "name": "Typescript" }]}

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