如何从邻接表树结构生成每个叶子节点的JSON路径？

节点表结构 #

我用邻接表存储了一棵多节点树，每条记录对应一个节点及其父节点，表结构如下：

需求说明 #

需要编写逻辑，为树中的每个叶子节点生成对应的JSON路径，预期结果如下：

目前已有能生成完整嵌套JSON树结构的函数，但我需要的是每个叶子节点单独的JSON路径。

PostgreSQL实现方案 #

1. 先通过递归CTE获取每个叶子节点的完整路径

WITH RECURSIVE node_paths AS (
    -- 从根节点开始初始化路径
    SELECT node_id, parent_id, ARRAY[node_id] AS path
    FROM your_table
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归遍历子节点，拼接完整路径
    SELECT child.node_id, child.parent_id, parent.path || child.node_id
    FROM your_table child
    JOIN node_paths parent ON child.parent_id = parent.node_id
),
leaf_nodes AS (
    -- 筛选出叶子节点（无下属子节点的节点）
    SELECT node_id AS leaf_id, path
    FROM node_paths np
    WHERE NOT EXISTS (
        SELECT 1 FROM your_table t WHERE t.parent_id = np.node_id
    )
)

2. 基于路径递归构建对应JSON结构
   将上述CTE与递归JSON生成逻辑结合，最终得到每个叶子节点的单独JSON路径：

SELECT 
    leaf_id,
    (
        WITH RECURSIVE json_builder(path_items, current_json) AS (
            -- 从叶子节点初始化最内层JSON
            SELECT path, json_build_object('name', last(path), 'children', '[]'::json)
            FROM leaf_nodes
            UNION ALL
            -- 递归向上拼接父节点的JSON结构
            SELECT 
                path_items[1:array_length(path_items, 1)-1],
                json_build_object(
                    'name', last(path_items[1:array_length(path_items, 1)-1]),
                    'children', json_build_array(current_json)
                )
            FROM json_builder
            WHERE array_length(path_items, 1) > 1
        )
        -- 提取根节点层级的最终JSON结果
        SELECT current_json FROM json_builder WHERE array_length(path_items, 1) = 1
    ) AS json_path
FROM leaf_nodes;

注意将代码中的your_table替换为实际的表名。

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