Postgresql如何递归更新jsonb字段及嵌套数组内的指定key名称

你已经实现了顶层price字段的重命名，要同步修改breakdown数组内所有元素的对应键，可以通过数组展开、逐元素处理、再聚合回写的逻辑实现，无需提前知道数组长度。

实现SQL #

UPDATE test
SET js = jsonb_set(
    -- 第一步：完成顶层price到cost的重命名
    jsonb_set(js #- '{price}', '{cost}', js #> '{price}'),
    -- 第二步：指定要替换的字段路径为breakdown
    '{breakdown}',
    -- 第三步：处理数组内所有元素
    (
        SELECT jsonb_agg(
            -- 单个元素执行键重命名逻辑：删除price，新增cost对应原来的price值
            elem #- '{price}' || jsonb_build_object('cost', elem -> 'price')
        )
        FROM jsonb_array_elements(js -> 'breakdown') AS elem
    )
);

逻辑说明 #

• 用jsonb_array_elements函数将breakdown数组拆分为单个元素的行集，不管数组有多少个元素都能全覆盖
• 对每个元素执行和顶层一样的重命名操作：先移除price键，再拼接新的cost键值对
• 用jsonb_agg将处理完成的元素重新聚合为数组
• 最后通过jsonb_set将新数组写回原json的breakdown路径，完成全量更新

优化兼容（可选） #

如果存在部分数据没有breakdown字段的场景，可以加COALESCE避免子查询返回空导致更新异常：

UPDATE test
SET js = jsonb_set(
    jsonb_set(js #- '{price}', '{cost}', js #> '{price}'),
    '{breakdown}',
    COALESCE(
        (
            SELECT jsonb_agg(elem #- '{price}' || jsonb_build_object('cost', elem -> 'price'))
            FROM jsonb_array_elements(js -> 'breakdown') AS elem
        ),
        js -> 'breakdown' -- 没有breakdown字段时保留原值
    )
);

执行结果验证 #

执行更新后执行查询语句：

SELECT id, js FROM test ORDER BY id;

得到的结果如下：

1 {"id": "total", "cost": 400, "breakdown": [{"id": "product1", "cost": 400}]}
2 {"id": "total", "cost": 1000, "breakdown": [{"id": "product1", "cost": 400}, {"id": "product2", "cost": 600}]}

内容的提问来源于stack exchange，提问作者Ambrozie Beniamin Paval