PostgreSQL14中如何对树结构jsonb字段递归查询指定节点的所有子节点

该需求完全可以实现，你可以使用PostgreSQL内置的递归CTE（WITH RECURSIVE语法）配合jsonb操作函数完成查询，具体操作如下：

前置说明 #

假设你存储jsonb数据的表名为tree_data，如果实际表名不同，替换SQL中的对应表名即可。

完整查询SQL #

WITH RECURSIVE find_target AS (
    -- 非递归项：从根节点开始遍历
    SELECT 
        root AS node,
        root->>'code' AS code
    FROM tree_data
    UNION ALL
    -- 递归项：逐层展开所有子节点，定位目标节点
    SELECT 
        jsonb_array_elements(td.node->'children') AS node,
        jsonb_array_elements(td.node->'children')->>'code' AS code
    FROM find_target td
    WHERE jsonb_array_length(td.node->'children') > 0
),
traverse_children AS (
    -- 非递归项：取目标节点的直接子节点作为初始遍历集合
    SELECT 
        jsonb_array_elements(ft.node->'children') AS child_node
    FROM find_target ft
    WHERE ft.code = '2' -- 此处修改为你要查询的目标节点code即可
    UNION ALL
    -- 递归项：逐层展开所有子孙节点，直到没有子节点为止
    SELECT 
        jsonb_array_elements(tc.child_node->'children') AS child_node
    FROM traverse_children tc
    WHERE jsonb_array_length(tc.child_node->'children') > 0
)
-- 输出指定字段
SELECT 
    child_node->>'code' AS code,
    child_node->>'name' AS name
FROM traverse_children
ORDER BY code;

逻辑说明 #

• 第一个递归CTE find_target 会遍历整个JSON树的所有节点，无论目标节点在哪个层级都可以精准定位
• 第二个递归CTE traverse_children 从目标节点的直接子节点开始，递归打平所有层级的子孙节点，children为空时自动终止递归，不会出现死循环
• 最终查询直接提取子节点的code、name字段，返回结果和你给出的预期完全一致

运行结果 #

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