PostgreSQL中为jsonb数组内所有对象新增键值对时UPDATE查询引发数据重复的问题排查

你的UPDATE语句出现异常的核心原因是没有将子查询中的行与主表的行进行关联，导致子查询把表中所有行的数组元素全部聚合到一起，然后将这个混合了所有行元素的数组赋值给每一行，这就解释了为什么会出现大量额外的数组元素。

具体来说，子查询FROM myTable, jsonb_array_elements(data)会生成表中所有行的数组元素的笛卡尔积，当表中有多行时，jsonb_agg会把所有这些元素合并成一个超大数组，最后这个数组会被更新到每一行的data字段，自然就混入了不属于当前行的元素。

方案1：通过ID关联分组（通用写法） #

我们需要在子查询中按唯一标识ID分组，并且在UPDATE时通过ID关联主表和子查询，确保每一行只处理自己的数组元素：

UPDATE myTable t
SET data = d.json_array
FROM (
  SELECT 
    id,
    jsonb_agg(
      jsonb_set(elems, '{valueC}', elems->'valueA')
    ) as json_array
  FROM myTable, jsonb_array_elements(data) elems
  GROUP BY id  -- 按ID分组，确保每个ID只聚合自己的元素
) d
WHERE t.id = d.id;  -- 关联主表和子查询的ID，保证更新对应行

方案2：使用相关子查询（更简洁推荐） #

如果你的PostgreSQL版本在9.5及以上（支持jsonb的||合并运算符），可以用更简洁的相关子查询写法，它会自动关联当前行的data字段，避免手动关联的错误：

UPDATE myTable
SET data = (
  SELECT jsonb_agg(
    elems || jsonb_build_object('valueC', elems->'valueA')
  )
  FROM jsonb_array_elements(data) elems
);

这个写法的逻辑是：

1. jsonb_array_elements(data)展开当前行的数组元素
2. elems || jsonb_build_object('valueC', elems->'valueA')将新的valueC键值对合并到原有元素中
3. jsonb_agg将处理后的元素重新聚合成数组，赋值给当前行的data字段

两种方案都会为每个数组元素新增valueC字段，且值与对应元素的valueA一致，同时保证每一行只处理自己的数组元素，不会出现跨行元素混合的问题。

内容的提问来源于stack exchange，提问作者Franjo Pintarić