如何从JSONPath的.**查询中提取可用于JSONB数据编辑的路径？

核心思路 #

你可以通过递归CTE遍历整棵JSONB树，同时记录每一层的路径，最终筛选出所有匹配目标键的路径，得到的text[]类型路径可以直接传给jsonb_set函数使用，完全适配任意深度的嵌套结构。

步骤1：查询所有匹配目标键的完整路径 #

假设你的业务表名为my_table，主键字段为id，存储JSONB的字段名为content，要查找的目标键为other_val_x，查询语句如下：

WITH RECURSIVE json_traverse AS (
    -- 根节点初始化
    SELECT
        t.id AS row_id,
        '{}'::text[] AS current_path,
        t.content AS current_value
    FROM my_table t
    -- 可在这里加WHERE条件过滤需要处理的行，例如 WHERE t.id = 123
    UNION ALL
    -- 遍历对象类型节点
    SELECT
        j.row_id,
        j.current_path || kv.key,
        kv.value
    FROM json_traverse j,
         jsonb_each(j.current_value) kv
    WHERE jsonb_typeof(j.current_value) = 'object'
    UNION ALL
    -- 遍历数组类型节点
    SELECT
        j.row_id,
        j.current_path || (arr.index - 1)::text,
        arr.value
    FROM json_traverse j,
         jsonb_array_elements(j.current_value) WITH ORDINALITY arr(value, index)
    WHERE jsonb_typeof(j.current_value) = 'array'
)
-- 筛选匹配目标键的路径
SELECT row_id, current_path, current_value AS old_value
FROM json_traverse
WHERE current_path[array_length(current_path, 1)] = 'other_val_x';

查询返回的current_path字段就是符合要求的路径数组，格式类似{data,0,other_val_x}，可直接传入jsonb_set。

步骤2：结合路径批量更新JSONB值 #

如果你需要直接更新所有匹配的other_val_x的值，可以直接扩展上面的查询为UPDATE语句，示例如下（更新所有other_val_x的值为new_value）：

WITH RECURSIVE json_traverse AS (
    SELECT
        t.id AS row_id,
        '{}'::text[] AS current_path,
        t.content AS current_value
    FROM my_table t
    UNION ALL
    SELECT
        j.row_id,
        j.current_path || kv.key,
        kv.value
    FROM json_traverse j,
         jsonb_each(j.current_value) kv
    WHERE jsonb_typeof(j.current_value) = 'object'
    UNION ALL
    SELECT
        j.row_id,
        j.current_path || (arr.index - 1)::text,
        arr.value
    FROM json_traverse j,
         jsonb_array_elements(j.current_value) WITH ORDINALITY arr(value, index)
    WHERE jsonb_typeof(j.current_value) = 'array'
),
matched_paths AS (
    SELECT row_id, current_path
    FROM json_traverse
    WHERE current_path[array_length(current_path, 1)] = 'other_val_x'
)
UPDATE my_table t
SET content = jsonb_set(t.content, mp.current_path, '"new_value"'::jsonb)
FROM matched_paths mp
WHERE t.id = mp.row_id;

注意事项 #

• 如果你的JSONB嵌套深度超过1000，需要先调整PostgreSQL递归深度限制，可以在当前会话执行SET max_recursion_depth = 10000;临时调高限制。
• 如果你需要匹配更复杂的路径规则，可修改matched_paths的筛选条件，例如要匹配所有data节点下的other_val_x，可以增加条件AND current_path @> ARRAY['data']。

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