如何修改Postgres中存储的jsonb数据结构 调整数组元素为对象格式

该需求完全可以通过Postgres原生JSONB操作函数实现，无需额外外部脚本。

前置说明 #

执行更新操作前请务必完成以下准备：

• 先备份全表数据，避免误操作导致数据丢失
• 先在测试环境执行查询语句验证转换结果符合预期，再执行生产更新

步骤1：执行查询验证转换结果 #

你可以先执行以下查询，确认转换后的JSON结构符合要求，注意将your_table_name替换为你实际的表名：

SELECT 
  Id,
  jsonb_build_object(
    'DestinationLists', 
    jsonb_agg(
      jsonb_set(
        list_element, 
        '{destinations}',
        -- 将数字数组转为Id对象数组
        (
          SELECT jsonb_agg(jsonb_build_object('Id', dest_num::int))
          FROM jsonb_array_elements_text(list_element -> 'destinations') AS dest_num
        )
      )
    )
  ) AS converted_json
FROM 
  your_table_name,
  jsonb_array_elements(JsonValue -> 'DestinationLists') AS list_element
GROUP BY Id;

步骤2：执行更新操作 #

验证结果无误后，执行以下更新语句完成全表JsonValue字段的转换：

UPDATE your_table_name t
SET JsonValue = (
  SELECT jsonb_build_object(
    'DestinationLists', 
    jsonb_agg(
      jsonb_set(
        list_element, 
        '{destinations}',
        (
          SELECT jsonb_agg(jsonb_build_object('Id', dest_num::int))
          FROM jsonb_array_elements_text(list_element -> 'destinations') AS dest_num
        )
      )
    )
  )
  FROM jsonb_array_elements(t.JsonValue -> 'DestinationLists') AS list_element
);

特殊场景适配 #

如果你的表中存在部分行没有DestinationLists字段、或者destinations结构不符合要求的情况，可以在更新语句末尾增加WHERE条件过滤，仅处理符合要求的行：

-- 示例：仅处理存在DestinationLists字段的行
WHERE t.JsonValue ? 'DestinationLists'

内容的提问来源于stack exchange，提问作者Fakhar Ahmad Rasul