PostgreSQL如何将JSON列内指定键的字符串值替换为对象

实现方案 #

你的需求可以通过PostgreSQL的JSONB操作函数实现，步骤如下：

1. 先执行查询验证修改结果是否符合预期（请把语句中你的表名替换为实际表名）：

SELECT 
  jsonb_set(
    additional_info::jsonb,
    '{dbServiceAccount, aggregationMode}',
    jsonb_build_object('name', additional_info::jsonb -> 'dbServiceAccount' ->> 'aggregationMode')
  )::text AS modified_additional_info
FROM 你的表名;

2. 确认结果正确后，执行更新语句修改正式数据，语句自带筛选条件只会修改存在dbServiceAccount.aggregationMode路径的行，避免无意义更新：

UPDATE 你的表名
SET additional_info = jsonb_set(
    additional_info::jsonb,
    '{dbServiceAccount, aggregationMode}',
    jsonb_build_object('name', additional_info::jsonb -> 'dbServiceAccount' ->> 'aggregationMode')
  )::text
WHERE 
  additional_info::jsonb ? 'dbServiceAccount' 
  AND (additional_info::jsonb -> 'dbServiceAccount') ? 'aggregationMode';

注意事项 #

• 正式操作前请务必备份全表数据，建议先新增筛选条件（比如AND id = 测试行ID）先修改单行验证效果，再全量执行。
• 如果后续需要频繁操作该列的JSON内容，建议直接将additional_info列类型修改为jsonb，无需每次强转，性能更高还支持索引优化。
• 该方案适用于PostgreSQL 9.4及以上版本，低于该版本需要先升级数据库版本。

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