Postgres如何通过单次查询更新多层jsonb的多个元素？

背景 #

假设Postgres中有一张表，包含一个深度大于1的jsonb字段，示例数据如下：

{
    "k1":
    {
        "k1.1": "v1.1",
        "k1.2": "v1.2"
    },
    "k2":
    {
        "k2.1": "v2.1",
        "k2.2": "v2.2",
        "k2.3": "v2.3"
    }
}

问题 #

能否使用jsonb函数修改该字段，实现单次查询更新多个JSON元素？以上述示例为例，期望输出如下：

{
    "k1":
    {
        "k1.1": "v1.1-updated",
        "k1.2": "v1.2"
    },
    "k2":
    {
        "k2.1": "v2.1",
        "k2.2": "v2.2",
        "k2.3": "v2.3-updated"
    }
}

理想特性 #

• 适用于任意复杂度的JSON结构
• 添加更多JSON字段修改时，能良好扩展（即不会过多影响查询性能与可读性）

次优方案分析 #

1. 可实现目标，但扩展性差 #

通过多层嵌套jsonb_set可以完成修改，但新增修改项时必须持续嵌套函数，可读性和维护性会急剧下降：

jsonb_set(
  jsonb_set(value, '{k1,k1.1}', '"v1.1-updated"'::jsonb), 
  '{k2,k2.3}', '"v2.3-updated"'::jsonb)

2. 语法扩展性佳，但无法实现修改目标 #

#-操作符仅用于删除JSON字段，不能完成值的修改：

value
  #- '{k1,k1.1}'
  #- '{k2,k2.3}'

最优解决方案：使用jsonb_merge_patch #

PostgreSQL的jsonb_merge_patch函数完美匹配需求，它通过补丁JSON与原JSON合并的方式，批量覆盖指定字段的值，无需嵌套函数，扩展性极强。

基础用法示例 #

UPDATE your_table
SET jsonb_column = jsonb_merge_patch(
    jsonb_column,
    '{
        "k1": {"k1.1": "v1.1-updated"},
        "k2": {"k2.3": "v2.3-updated"}
    }'::jsonb
)
WHERE id = your_target_id;

方案优势 #

• 适配任意JSON复杂度：不管JSON嵌套层级多深，只需构造对应结构的补丁JSON，就能精准修改目标字段。
• 扩展性拉满：新增修改项时，只需在补丁JSON中添加对应键值对即可，完全不影响原有代码结构，可读性和维护性都很好。
• 性能高效：作为PostgreSQL原生优化函数，jsonb_merge_patch的批量修改性能优于多层嵌套的jsonb_set。

动态修改场景扩展 #

如果修改内容需要动态生成（比如从其他表获取更新数据），可以结合jsonb_object_agg构造补丁JSON，灵活性更高：

WITH update_items AS (
    SELECT 
        array['k1', 'k1.1'] AS path,
        'v1.1-updated'::text AS new_val
    UNION ALL
    SELECT
        array['k2', 'k2.3'] AS path,
        'v2.3-updated'::text AS new_val
)
UPDATE your_table
SET jsonb_column = (
    SELECT jsonb_merge_patch(jsonb_column, jsonb_object_agg(path, new_val))
    FROM update_items
)
WHERE id = your_target_id;

这种方式下，新增修改项只需在update_items中添加一行数据即可，完全满足扩展需求。

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