基于MariaDB 11.4递归CTE生成父子关系单JSON对象的SQL查询需求

前提假设 #

假设你的父子关系表名为category，结构如下（可根据实际字段调整）：

CREATE TABLE category (
    id INT PRIMARY KEY,
    parent_id INT NULL COMMENT '父节点ID，根节点为NULL',
    category_name VARCHAR(100) NOT NULL COMMENT '节点名称'
);

递归CTE+JSON聚合实现方案 #

以下SQL会将树形数据转换为嵌套JSON结构（每个节点包含id、name和children数组）：

WITH RECURSIVE category_tree AS (
    -- 1. 锚点查询：获取所有叶子节点，生成基础JSON结构
    SELECT 
        id,
        parent_id,
        category_name,
        JSON_OBJECT(
            'id', id,
            'name', category_name,
            'children', JSON_ARRAY()
        ) AS json_node
    FROM category
    WHERE id NOT IN (SELECT DISTINCT parent_id FROM category WHERE parent_id IS NOT NULL)

    UNION ALL

    -- 2. 递归查询：向上关联父节点，合并子节点的JSON数组
    SELECT 
        p.id,
        p.parent_id,
        p.category_name,
        JSON_OBJECT(
            'id', p.id,
            'name', p.category_name,
            'children', JSON_ARRAYAGG(ct.json_node)
        ) AS json_node
    FROM category p
    JOIN category_tree ct ON p.id = ct.parent_id
    GROUP BY p.id, p.parent_id, p.category_name
)
-- 3. 提取根节点的完整JSON结构
SELECT json_node AS full_tree_json
FROM category_tree
WHERE parent_id IS NULL;

关键部分说明 #

• 锚点查询：先定位没有子节点的叶子节点，将每个节点转换为children为空数组的基础JSON对象。
• 递归查询：将父节点与已处理好的子节点JSON关联，用JSON_ARRAYAGG把所有子节点JSON聚合成数组，赋值给父节点的children字段。
• 最终筛选：只取出根节点（parent_id IS NULL）的JSON，得到完整的树形结构。

扩展场景处理 #

如果存在多个根节点，想要生成包含所有根节点的JSON数组，将最后一步替换为：

SELECT JSON_ARRAYAGG(json_node) AS full_tree_json
FROM category_tree
WHERE parent_id IS NULL;

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