PostgreSQL 15中修改JSON字段内容结构的方法

你可以通过PostgreSQL内置的json_build_object函数直接实现需求，该函数能快速构建指定结构的JSON对象。

1. 查询时生成目标结构（不修改原表数据） #

如果只是需要查询输出目标格式的数据，执行以下SQL：

SELECT 
  id,
  json_build_object(
    'parameters', data,
    'settings', '[]'::json
  ) AS formatted_data
FROM mytable;

语句说明： #

• json_build_object(key, value...)：按传入的键值对组合成JSON对象
• 'parameters', data：将原表的data字段（JSON数组）直接映射为parameters键的值
• 'settings', '[]'::json：创建空JSON数组作为settings键的值，::json用于将字符串转换为JSON类型

2. 更新原表字段为目标结构 #

如果需要直接修改原表的data字段，让其存储目标结构，执行以下UPDATE语句（执行前建议备份数据或先通过SELECT验证结果）：

UPDATE mytable
SET data = json_build_object(
  'parameters', data,
  'settings', '[]'::json
);

补充说明 #

如果你的data字段类型是jsonb（PostgreSQL推荐使用的JSON类型，支持索引和更高效的操作），只需将json_build_object替换为jsonb_build_object即可，逻辑完全一致。

内容的提问来源于stack exchange，提问作者Davide D.