如何在PostgreSQL中更新含列表的JSON数据（迁移场景）

假设你的数据表名为your_table，存储JSON数据的字段名为config（推荐使用jsonb类型以支持修改操作，若为json类型需额外做类型转换），以下是满足需求的两种实现方案：

方法一：使用JSONPath（PostgreSQL 12+） #

适合PostgreSQL 12及以上版本，利用原生JSONPath语法简化操作：

UPDATE your_table
SET config = jsonb_insert(
  -- 操作1：将switches数组中displayName为"Name"的元素值替换为"ProductName"
  jsonb_path_set(
    config,
    '$.tableCache.switches[*] ? (@.displayName == "Name").displayName',
    '"ProductName"'
  ),
  -- 操作2：在switchButtons数组的第二个位置（索引1）插入指定新条目
  '{tableCache,switchButtons,1}',
  '{"flex": 4, "name": "wwn", "tooltip": true, "isStatic": false, "selected": true, "sortable": true, "filterable": true, "displayName": "WWN"}'::jsonb
);

代码说明 #

• jsonb_path_set：通过JSONPath表达式精准定位目标元素，自动匹配switches数组中所有displayName为"Name"的条目并修改对应字段值。
• jsonb_insert：在switchButtons数组的索引1位置（JSON数组索引从0开始，对应第二个位置）插入指定的JSON对象。

方法二：兼容低版本PostgreSQL（9.6+） #

若你的PostgreSQL版本低于12，无法使用JSONPath，可通过数组遍历重组实现需求：

UPDATE your_table
SET config = jsonb_insert(
  -- 操作1：修改switches数组中的目标元素
  jsonb_set(
    config,
    '{tableCache,switches}',
    (
      SELECT jsonb_agg(
        CASE
          WHEN elem->>'displayName' = 'Name' THEN elem || '{"displayName": "ProductName"}'::jsonb
          ELSE elem
        END
      )
      FROM jsonb_array_elements(config->'tableCache'->'switches') AS elem
    )
  ),
  -- 操作2：插入新条目到switchButtons数组
  '{tableCache,switchButtons,1}',
  '{"flex": 4, "name": "wwn", "tooltip": true, "isStatic": false, "selected": true, "sortable": true, "filterable": true, "displayName": "WWN"}'::jsonb
);

代码说明 #

• jsonb_array_elements：将switches数组拆分为单个元素，遍历判断每个元素的displayName字段，符合条件则替换该字段值。
• jsonb_agg：将处理后的元素重新组合为数组，再通过jsonb_set替换原数组。
• jsonb_insert的作用同方法一，完成数组插入操作。

注意事项 #

• 若字段为json类型，需在操作前后添加类型转换（如config::jsonb，最终结果转::json）。
• 替换语句中的your_table和config为实际的表名和JSON字段名。

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