PostgreSQL 10中如何更新嵌套JSON内的组件数据

首先，你之前尝试的语法不符合PostgreSQL的JSON操作规范，咱们得用PostgreSQL原生的JSON/JSONB函数来处理嵌套数组的修改。假设你的parts表中有一个存储嵌套JSON数组的列（比如叫nested_parts，这里推荐用jsonb类型，它比json更适合做修改操作），下面是具体的实现方案：

针对JSONB类型列的更新语句 #

如果你的嵌套JSON列是jsonb类型，执行以下语句就能同步更新数组中id=2的对象的name为Part X：

UPDATE parts
SET nested_parts = (
  -- 将修改后的JSON元素重新聚合成数组
  SELECT jsonb_agg(
    -- 判断当前元素是否是目标id，是则更新name字段
    CASE WHEN elem->>'id' = '2'
         THEN elem || '{"name": "Part X"}'::jsonb  -- 用||合并覆盖原有name
         ELSE elem
    END
  )
  -- 将JSON数组拆分为单个元素行，方便逐个处理
  FROM jsonb_array_elements(nested_parts) AS elem
)
-- 只更新包含id=2元素的行，避免无意义的更新操作
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(nested_parts) AS elem
  WHERE elem->>'id' = '2'
);

针对JSON类型列的更新语句 #

如果你的列是json类型（这是只读类型，修改需要先转换类型），可以先转成jsonb处理后再转回json：

UPDATE parts
SET nested_parts = (
  SELECT json_agg(
    CASE WHEN (elem->>'id')::int = 2
         THEN (elem::jsonb || '{"name": "Part X"}'::jsonb)::json
         ELSE elem
    END
  )
  FROM json_array_elements(nested_parts) AS elem
)
WHERE EXISTS (
  SELECT 1
  FROM json_array_elements(nested_parts) AS elem
  WHERE (elem->>'id')::int = 2
);

关键逻辑说明 #

1. 展开数组：用jsonb_array_elements（或json_array_elements）把JSON数组拆分成独立的对象行，这样就能逐个处理每个元素。
2. 条件修改：通过CASE语句定位id=2的对象，用||操作符合并新的name键值对——这个操作会自动覆盖原有对象中的name字段。
3. 重新聚合：用jsonb_agg（或json_agg）把修改后的对象重新组合成JSON数组，恢复原来的嵌套结构。
4. 过滤更新：用WHERE EXISTS确保只更新包含目标元素的行，提升执行效率。

额外提示 #

如果需要同时更新name字段和嵌套JSON数据，可以把两个操作合并到一个UPDATE语句中，避免多次执行：

UPDATE parts
SET 
  name = 'Part X',
  nested_parts = (
    SELECT jsonb_agg(
      CASE WHEN elem->>'id' = '2'
           THEN elem || '{"name": "Part X"}'::jsonb
           ELSE elem
      END
    )
    FROM jsonb_array_elements(nested_parts) AS elem
  )
WHERE id = 2 OR EXISTS (
  SELECT 1
  FROM jsonb_array_elements(nested_parts) AS elem
  WHERE elem->>'id' = '2'
);

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