将SQL父子表记录转换为树形结构的实现方案咨询

问题背景 #

现有层级结构SQL表（RowID为主键，ParentID关联父记录RowID），数据如下：

需要将其转换为指定树形JSON结构：

[{"name": "Fruit", "children": [{"name": "Apple"}, {"name": "Peach"}]}, {"name": "Veggie", "children": [{"name": "Corn", "children": [{"name": "Sweet Corn"}]}]}]

实现方案 #

方案1：数据库端直接生成（分数据库示例）

PostgreSQL 版本

利用递归CTE+JSON聚合函数直接输出目标JSON：

WITH RECURSIVE category_tree AS (
    -- 筛选根节点（ParentID=0）并初始化JSON结构
    SELECT 
        RowID,
        Name,
        ParentID,
        jsonb_build_object('name', Name, 'children', '[]'::jsonb) AS json_data
    FROM your_table
    WHERE ParentID = 0

    UNION ALL

    -- 递归遍历所有子节点
    SELECT 
        c.RowID,
        c.Name,
        c.ParentID,
        jsonb_build_object('name', c.Name, 'children', '[]'::jsonb) AS json_data
    FROM your_table c
    JOIN category_tree ct ON c.ParentID = ct.RowID
),
-- 按父节点分组聚合子节点数据
aggregated_tree AS (
    SELECT 
        ParentID,
        jsonb_agg(json_data) AS children
    FROM category_tree
    GROUP BY ParentID
)
-- 组装根节点的最终JSON结构
SELECT jsonb_agg(
    CASE 
        WHEN at.children IS NOT NULL THEN ct.json_data || jsonb_build_object('children', at.children)
        ELSE ct.json_data
    END
)::text AS tree_json
FROM category_tree ct
LEFT JOIN aggregated_tree at ON ct.RowID = at.ParentID
WHERE ct.ParentID = 0;

MySQL 8.0+ 版本

通过递归CTE结合JSON函数实现：

WITH RECURSIVE category_tree AS (
    -- 初始化根节点JSON
    SELECT 
        RowID,
        Name,
        ParentID,
        JSON_OBJECT('name', Name) AS json_data
    FROM your_table
    WHERE ParentID = 0

    UNION ALL

    -- 递归获取子节点
    SELECT 
        c.RowID,
        c.Name,
        c.ParentID,
        JSON_OBJECT('name', c.Name) AS json_data
    FROM your_table c
    JOIN category_tree ct ON c.ParentID = ct.RowID
),
-- 分组聚合子节点数组
aggregated AS (
    SELECT 
        ParentID,
        JSON_ARRAYAGG(json_data) AS children
    FROM category_tree
    GROUP BY ParentID
)
-- 合并父节点与子节点数据
SELECT JSON_ARRAYAGG(
    IF(a.children IS NOT NULL, JSON_MERGE_PRESERVE(ct.json_data, JSON_OBJECT('children', a.children)), ct.json_data)
) AS tree_json
FROM category_tree ct
LEFT JOIN aggregated a ON ct.RowID = a.ParentID
WHERE ct.ParentID = 0;

方案2：应用端读取后组装（Python示例）

如果数据库不支持复杂JSON操作，可先查询全量数据，再在应用层组装树形结构：

1. 先执行SQL查询全量数据：

SELECT RowID, ParentID, Name FROM your_table;

2. Python代码组装树形结构：

# 假设从数据库获取的原始数据如下（实际项目中替换为数据库查询结果）
raw_data = [
    {"RowID": 1, "ParentID": 0, "Name": "Fruit"},
    {"RowID": 2, "ParentID": 1, "Name": "Apple"},
    {"RowID": 3, "ParentID": 1, "Name": "Peach"},
    {"RowID": 4, "ParentID": 0, "Name": "Veggie"},
    {"RowID": 5, "ParentID": 4, "Name": "Corn"},
    {"RowID": 6, "ParentID": 5, "Name": "Sweet Corn"},
]

# 构建节点映射表，快速查找父节点
node_map = {}
tree_root = []

for item in raw_data:
    # 创建当前节点
    current_node = {"name": item["Name"]}
    node_map[item["RowID"]] = current_node
    
    # 根节点直接加入结果，非根节点关联到父节点的children数组
    parent_id = item["ParentID"]
    if parent_id == 0:
        tree_root.append(current_node)
    else:
        parent_node = node_map.get(parent_id)
        if parent_node:
            parent_node.setdefault("children", []).append(current_node)

# 输出目标JSON
import json
print(json.dumps(tree_root, indent=2))

其他语言（Java、JavaScript等）的实现思路一致：先构建节点映射，再按ParentID关联子节点，最终收集根节点即可。

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