PostgreSQL 15.2中JSON数组元素批量更新问题求助

问题根源 #

你当前的SQL写法存在关键问题：当items CTE返回多条匹配路径时，UPDATE ... FROM语句只会选取其中一条路径执行更新（PostgreSQL处理多源匹配单目标行时，仅保留一次更新操作），导致只有第一个或最后一个符合条件的元素被修改，无法批量更新数组中所有匹配项。

另外，原SQL里使用jsonb_array_elements_text后再转成JSONB属于多余操作，直接用jsonb_array_elements更高效。

解决方案一：重新生成JSON数组（推荐） #

这种方法通过拆分数组、修改元素、重新聚合的方式实现批量更新，逻辑直观且性能更优：

UPDATE my_table
SET dashboard = (
  SELECT jsonb_agg(
    CASE WHEN (elem ->> 'state') IN ('running', 'iddle')
         THEN elem || '{"state": "canceled"}'::jsonb
         ELSE elem
    END
  )
  FROM jsonb_array_elements(dashboard) AS elem
)
WHERE id = 5;

逻辑说明：

1. jsonb_array_elements(dashboard)将JSON数组拆分为单个JSON元素；
2. CASE语句判断元素的state是否符合条件，符合则用||操作符替换state字段（JSONB的合并操作会覆盖同名键）；
3. jsonb_agg将修改后的元素重新聚合成数组，替换原dashboard字段。

解决方案二：递归应用jsonb_set（适合复杂场景） #

如果需要基于原数组逐步修改（而非重新生成），可以用递归CTE依次执行每个jsonb_set操作：

WITH RECURSIVE updates AS (
  SELECT 
    id,
    dashboard,
    -- 收集所有需要修改的路径
    ARRAY(
      SELECT ('{'||index-1||',state}')::TEXT[]
      FROM jsonb_array_elements(dashboard) WITH ORDINALITY arr(item, index)
      WHERE (item ->> 'state') IN ('running', 'iddle')
    ) AS paths,
    1 AS idx
  FROM my_table
  WHERE id = 5
  UNION ALL
  SELECT
    id,
    -- 基于上一次修改后的结果执行下一次jsonb_set
    jsonb_set(dashboard, paths[idx], '"canceled"'::jsonb, false),
    paths,
    idx + 1
  FROM updates
  WHERE idx <= array_length(paths, 1)
)
UPDATE my_table
SET dashboard = updates.dashboard
FROM updates
WHERE my_table.id = updates.id
-- 取递归到最后一步的结果
AND idx = array_length(paths, 1);

逻辑说明：

1. 初始递归步骤收集所有需要修改的路径；
2. 递归过程中依次对dashboard应用每个路径的jsonb_set，每次修改都基于上一次的结果；
3. 最后用递归到最终状态的dashboard更新原表。

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