如何用PostgreSQL批量更新JSONB字典中的多个嵌套值

针对你的场景——表结构无法调整，steps字段是jsonb类型，顶层包含多个uuid键，每个uuid下ONE->TWO->THREE->FOUR->value1为数组格式，需要批量将单行内所有这类value1转为标量值——可以用以下SQL方案实现批量更新：

核心更新语句 #

UPDATE your_table t
SET steps = (
  SELECT jsonb_object_agg(
    uuid_key,
    jsonb_set(
      uuid_value,
      '{ONE,TWO,THREE,FOUR,value1}',
      -- 取数组第一个元素作为标量，若需其他逻辑可修改此处
      (uuid_value #> '{ONE,TWO,THREE,FOUR,value1}') -> 0
    )
  )
  FROM jsonb_each(t.steps) AS uuid_entries(uuid_key, uuid_value)
)
-- 可选：仅更新包含目标嵌套结构的行，避免无效更新
WHERE EXISTS (
  SELECT 1
  FROM jsonb_each(t.steps) AS uuid_entries(uuid_key, uuid_value)
  WHERE uuid_value #> '{ONE,TWO,THREE,FOUR,value1}' IS NOT NULL
);

关键逻辑说明 #

• jsonb_each(t.steps)：遍历steps顶层的所有uuid键值对，把每个独立的uuid条目拆出来单独处理
• jsonb_set：定位到每个uuid条目下的ONE->TWO->THREE->FOUR->value1路径，将原数组替换为数组的第一个元素（-> 0），保证输出是jsonb标量
• jsonb_object_agg：把处理完的所有uuid键值对重新聚合为完整的jsonb对象，替换原steps字段
• WHERE子句：通过EXISTS判断行内是否存在目标嵌套结构，只对有需要更新的行执行操作，提升效率

扩展调整方案 #

1. 处理空数组场景：如果value1可能是空数组，避免更新后出现null，可以用COALESCE设置默认值：

-- 示例：空数组时设为空字符串
COALESCE((uuid_value #> '{ONE,TWO,THREE,FOUR,value1}') -> 0, '""'::jsonb)

2. 取数组其他元素：如果需要取数组最后一个元素，把-> 0改为-> '-1'：

(uuid_value #> '{ONE,TWO,THREE,FOUR,value1}') -> '-1'

3. 先验证再更新：执行更新前，可以先运行查询查看处理后的结果，确保符合预期：

SELECT
  id, -- 替换为你的表主键字段
  steps AS original_steps,
  (
    SELECT jsonb_object_agg(
      uuid_key,
      jsonb_set(
        uuid_value,
        '{ONE,TWO,THREE,FOUR,value1}',
        (uuid_value #> '{ONE,TWO,THREE,FOUR,value1}') -> 0
      )
    )
    FROM jsonb_each(t.steps) AS uuid_entries(uuid_key, uuid_value)
  ) AS updated_steps
FROM your_table t;

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