PostgreSQL按tree_id生成决策树嵌套JSON的函数问题

1. 排查递归SQL的核心问题 #

现有函数返回全量树结构的原因是：递归过程中未对tree_id做严格过滤，导致不同树的节点被混在一起关联。需要确保递归CTE的每一步都绑定目标tree_id。

2. 修改SQL函数，传入tree_id参数 #

以PostgreSQL为例，改造后的函数会接收tree_id参数，仅递归处理该树的节点：

CREATE OR REPLACE FUNCTION get_single_decision_tree(p_tree_id INT)
RETURNS JSON AS $$
WITH RECURSIVE node_hierarchy AS (
    -- 锚点：获取当前tree_id的根节点
    SELECT
        id,
        node_name,
        parent_id,
        tree_id,
        json_build_object(
            'id', id,
            'node_name', node_name,
            'children', '[]'::json
        ) AS node_json
    FROM prm_tree_nodes
    WHERE parent_id IS NULL AND tree_id = p_tree_id

    UNION ALL

    -- 递归：仅关联当前tree_id的子节点
    SELECT
        child.id,
        child.node_name,
        child.parent_id,
        child.tree_id,
        json_build_object(
            'id', child.id,
            'node_name', child.node_name,
            'children', '[]'::json
        ) AS node_json
    FROM prm_tree_nodes child
    JOIN node_hierarchy parent ON child.parent_id = parent.id AND child.tree_id = p_tree_id
),
-- 从最底层节点开始向上组装嵌套结构
assemble_tree AS (
    SELECT id, parent_id, node_json
    FROM node_hierarchy
    ORDER BY (SELECT MAX(level) FROM node_hierarchy) - (SELECT level FROM node_hierarchy WHERE id = node_hierarchy.id) DESC
)
SELECT json_agg(root_node.node_json)::json
FROM (
    SELECT
        jsonb_set(
            parent.node_json,
            '{children}',
            COALESCE(json_agg(child.node_json), '[]'::jsonb)
        ) AS node_json
    FROM assemble_tree parent
    LEFT JOIN assemble_tree child ON parent.id = child.parent_id
    WHERE parent.parent_id IS NULL
    GROUP BY parent.id, parent.node_json
) AS root_tree;
$$ LANGUAGE sql;

3. 调用函数获取专属树结构 #

传入指定tree_id，即可得到对应树的嵌套JSON：

SELECT get_single_decision_tree(101); -- 获取tree_id=101的决策树
SELECT get_single_decision_tree(102); -- 获取tree_id=102的决策树

关键注意事项 #

• 递归的每一步都要带上tree_id = p_tree_id的过滤条件，彻底隔离不同树的节点
• 若使用MySQL等其他数据库，替换对应JSON函数：比如用JSON_OBJECT替代json_build_object，JSON_ARRAYAGG替代json_agg
• 给prm_tree_nodes表的tree_id字段添加索引，提升递归查询的性能

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