PostgreSQL将嵌套jsonb扁平化为键值对并按行更新新增字段

原生PostgreSQL实现方案 #

以下是符合行级更新要求的写法，完全基于PSQL原生JSONB函数，无需外部工具，每一行的新字段值仅由当前行的旧字段转换生成，不会触发全表扫描逻辑：

UPDATE your_table t
SET new_field = (
  SELECT jsonb_object_agg(
    -- 保留你原有的CASE类型映射逻辑
    CASE (datasets ->> 'type')
      WHEN 'type_info' THEN 'type_info'
      -- 其余type的映射规则继续往下写即可
      ELSE datasets ->> 'type'
    END,
    datasets -> 'value'
  )
  -- 直接展开当前行旧字段中的keynamed数组
  FROM jsonb_array_elements(t.old_value -> 'keynamed') AS datasets
);

注：你给出的原JSON结构["keynamed": [...]]存在语法错误，标准JSON中数组不能直接存键值对，如果实际最外层是对象{"keynamed": [...]}, 上述写法直接可用；如果实际最外层确实是数组，调整jsonb_array_elements的参数为适配你实际结构的路径即可。

验证转换结果（执行更新前建议先运行） #

可以先通过SELECT语句预览转换效果，确认逻辑正确后再执行更新：

SELECT
  id,
  old_value,
  (
    SELECT jsonb_object_agg(
      CASE (datasets ->> 'type')
        WHEN 'type_info' THEN 'type_info'
        ELSE datasets ->> 'type'
      END,
      datasets -> 'value'
    )
    FROM jsonb_array_elements(t.old_value -> 'keynamed') AS datasets
  ) AS new_value_preview
FROM your_table t
LIMIT 10; -- 可先查看少量样本验证逻辑

大数据量表优化建议 #

如果表数据量较大，为避免长时间锁表，可以按主键范围分批执行更新：

UPDATE your_table t
SET new_field = (/* 同上转换逻辑 */)
WHERE id BETWEEN 1 AND 1000; -- 按需调整分批范围，多次执行直到所有数据更新完成

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