如何在PostgreSQL数据库中更新JSONB数组内的对象键名

核心原理 #

JSONB修改键名的通用实现逻辑为：先为JSON对象添加新键并复制旧键对应的值，再删除旧键。基础语法如下：

-- 单个JSON对象键重命名语法
json_object - 'old_key_name' || jsonb_build_object('new_key_name', json_object -> 'old_key_name')

针对数组场景，需要先将数组拆分为独立元素完成重命名处理，再重新聚合为数组，同时通过WITH ORDINALITY保证数组元素顺序和原结构一致。

步骤1：先运行验证查询确认输出符合预期 #

执行以下语句可预览修改后的结果，和需求匹配后再执行更新操作：

SELECT 
  uid,
  jsonb_agg(
    (elem - 'SpindSpeed_Med' - 'RapidOveride_Med') 
    || jsonb_build_object('SpindleSpeed', elem -> 'SpindSpeed_Med')
    || jsonb_build_object('RapidOverride', elem -> 'RapidOveride_Med')
    ORDER BY ordinality
  ) AS new_tooldata
FROM test,
     jsonb_array_elements(tooldata) WITH ORDINALITY arr(elem, ordinality)
GROUP BY uid
ORDER BY uid;

步骤2：执行更新语句修改表数据 #

确认预览结果正确后运行以下语句完成正式更新：

UPDATE test t
SET tooldata = (
  SELECT jsonb_agg(
    (elem - 'SpindSpeed_Med' - 'RapidOveride_Med') 
    || jsonb_build_object('SpindleSpeed', elem -> 'SpindSpeed_Med')
    || jsonb_build_object('RapidOverride', elem -> 'RapidOveride_Med')
    ORDER BY ordinality
  )
  FROM jsonb_array_elements(t.tooldata) WITH ORDINALITY arr(elem, ordinality)
-- 可选：增加过滤条件只处理包含待修改键的行，提升大表更新效率
-- WHERE tooldata @> '[{"SpindSpeed_Med": null}]' OR tooldata @> '[{"RapidOveride_Med": null}]'
);

兼容优化（可选） #

如果存在部分数组元素不包含待修改旧键的场景，可增加判断逻辑避免生成null值的新键：

UPDATE test t
SET tooldata = (
  SELECT jsonb_agg(
    CASE 
      WHEN elem ? 'SpindSpeed_Med' AND elem ? 'RapidOveride_Med'
      THEN (elem - 'SpindSpeed_Med' - 'RapidOveride_Med') 
           || jsonb_build_object('SpindleSpeed', elem -> 'SpindSpeed_Med')
           || jsonb_build_object('RapidOverride', elem -> 'RapidOveride_Med')
      ELSE elem
    END
    ORDER BY ordinality
  )
  FROM jsonb_array_elements(t.tooldata) WITH ORDINALITY arr(elem, ordinality)
);

其中?是PostgreSQL JSONB专属操作符，用于判断对象是否包含指定键。

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