如何用Oracle SQL/PLSQL生成动态层级的嵌套JSON数据？

方法一：纯SQL递归实现 #

假设你的数据表名为hierarchy_table，字段为ID(数字类型)、NAME(字符串类型)、PARENT_ID(数字类型，根节点为NULL)，可以通过递归CTE结合Oracle JSON函数实现动态层级JSON生成：

WITH recursive_hierarchy AS (
    -- 基础查询：获取所有叶子节点（无下级节点的节点）
    SELECT 
        h.ID,
        h.NAME,
        h.PARENT_ID,
        JSON_OBJECT(
            'ID' VALUE h.ID,
            'NAME' VALUE h.NAME
        ) AS json_obj
    FROM hierarchy_table h
    WHERE NOT EXISTS (SELECT 1 FROM hierarchy_table h2 WHERE h2.PARENT_ID = h.ID)
    
    UNION ALL
    
    -- 递归查询：向上聚合子节点的JSON
    SELECT 
        p.ID,
        p.NAME,
        p.PARENT_ID,
        JSON_OBJECT(
            'ID' VALUE p.ID,
            'NAME' VALUE p.NAME,
            'child' VALUE JSON_ARRAYAGG(r.json_obj ORDER BY r.ID)
        ) AS json_obj
    FROM hierarchy_table p
    JOIN recursive_hierarchy r ON p.ID = r.PARENT_ID
    GROUP BY p.ID, p.NAME, p.PARENT_ID
)
-- 最终聚合根节点（PARENT_ID为NULL的节点），生成外层JSON
SELECT JSON_OBJECT('data' VALUE JSON_ARRAYAGG(json_obj ORDER BY ID)) AS final_json
FROM recursive_hierarchy
WHERE PARENT_ID IS NULL;

代码说明 #

• 递归CTE从叶子节点开始向上构建，每个父节点聚合其子节点的JSON对象数组
• JSON_OBJECT用于构建单个节点的JSON结构，JSON_ARRAYAGG用于聚合子节点数组
• 最终筛选根节点（PARENT_ID为NULL）并聚合为外层的data数组

方法二：PL/SQL递归函数实现 #

如果需要更灵活的控制（比如自定义排序、特殊字段处理），可以使用PL/SQL递归函数：

CREATE OR REPLACE FUNCTION get_hierarchy_json(p_parent_id IN NUMBER) RETURN CLOB IS
    v_json CLOB;
BEGIN
    -- 聚合当前父节点下的所有子节点JSON
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'ID' VALUE ID,
            'NAME' VALUE NAME,
            'child' VALUE CASE WHEN EXISTS (SELECT 1 FROM hierarchy_table h2 WHERE h2.PARENT_ID = h.ID)
                               THEN get_hierarchy_json(h.ID)
                               ELSE NULL END
        ) ORDER BY ID
    ) INTO v_json
    FROM hierarchy_table h
    WHERE h.PARENT_ID = p_parent_id;
    
    RETURN v_json;
END;
/

-- 调用函数生成最终JSON
SELECT JSON_OBJECT('data' VALUE get_hierarchy_json(NULL)) AS final_json FROM dual;

代码说明 #

• 递归函数get_hierarchy_json接收父ID，返回该父节点下所有子节点的嵌套JSON数组
• 当节点存在子节点时，递归调用自身生成子节点的JSON结构；无节点时返回NULL（自动省略child字段）
• 最后通过JSON_OBJECT包装为外层的data结构

注意事项 #

• 确保表中PARENT_ID与ID的关联关系正确，无循环引用（否则递归会报错）
• 如果数据量较大，纯SQL方法的性能通常优于PL/SQL，建议优先使用纯SQL方案
• 若需要处理特殊字符或空值，可以在JSON_OBJECT中添加ABSENT ON NULL等参数控制输出

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