如何用Oracle SQL生成节点的层级JSON子结构？

我有一张存储元素及其父元素引用的表，表结构如下：

节点表（Nodes） #

我目前使用以下SQL语句获取节点的直接子节点：

SELECT n.ID as ID, n.PARENT_ID as PARENT_ID,
    (
        SELECT listagg(pn.ID,',') WITHIN GROUP (ORDER BY id) as CHILD_IDS 
        FROM NODES pn 
        WHERE pn.PARENT_ID = n.ID
    ) as CHILD_IDS  
FROM NODES n  

查询结果如下：

查询结果 #

但我需要生成子节点的层级结构，理想格式为类似如下的JSON对象。例如，节点2010的理想输出为：

[
  {
    "2007": []
  },
  {
    "2008": []
  },
  {
    "2009": [
      {
        "2002": []
      },
      {
        "2003": []
      }
    ]
  }
]

请问该如何生成此类结构？我不知从何入手。

方案一：用Oracle原生JSON函数直接生成（Oracle 12c+） #

如果使用Oracle数据库，可以结合递归查询和JSON聚合函数直接生成目标结构，无需额外应用层处理：

方式1：递归函数实现

先创建一个递归函数来生成子节点的JSON数组：

CREATE OR REPLACE FUNCTION get_node_children(p_parent_id NUMBER) RETURN JSON_ARRAY IS
    v_child_json JSON_ARRAY;
BEGIN
    -- 递归查询当前节点的所有子节点，并生成嵌套JSON
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            TO_CHAR(ID) VALUE get_node_children(ID)
        ) ORDER BY ID
    ) INTO v_child_json
    FROM NODES
    WHERE PARENT_ID = p_parent_id;

    -- 没有子节点时返回空数组
    RETURN COALESCE(v_child_json, JSON_ARRAY());
END;
/

然后查询指定节点的层级结构：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        TO_CHAR(ID) VALUE get_node_children(ID)
    ) ORDER BY ID
) AS hierarchy_json
FROM NODES
WHERE PARENT_ID = 2010; -- 替换成你需要的父节点ID

执行后会直接输出符合要求的JSON结构。

方式2：纯CTE递归实现（无需创建函数）

如果不想创建函数，可以用递归CTE结合JSON函数完成：

WITH node_hierarchy AS (
    -- 从叶子节点开始，向上构建JSON
    SELECT 
        ID,
        PARENT_ID,
        JSON_OBJECT(TO_CHAR(ID) VALUE JSON_ARRAY()) AS node_json
    FROM NODES
    WHERE ID NOT IN (SELECT PARENT_ID FROM NODES WHERE PARENT_ID IS NOT NULL)
    UNION ALL
    SELECT 
        parent.ID,
        parent.PARENT_ID,
        JSON_OBJECT(
            TO_CHAR(parent.ID) VALUE JSON_ARRAYAGG(child.node_json ORDER BY child.ID)
        ) AS node_json
    FROM NODES parent
    JOIN node_hierarchy child ON parent.ID = child.PARENT_ID
    GROUP BY parent.ID, parent.PARENT_ID
)
-- 提取目标节点的子节点数组
SELECT JSON_ARRAYAGG(node_json ORDER BY ID) AS hierarchy_json
FROM node_hierarchy
WHERE PARENT_ID = 2010;

方案二：应用层处理（通用所有数据库） #

如果数据库的JSON生成能力有限，或者需要更灵活的控制，可以先获取完整的节点关系，再在应用代码中构建层级结构：

1. 先通过递归SQL获取所有节点的父子关系：

WITH recursive_nodes AS (
    SELECT ID, PARENT_ID FROM NODES WHERE PARENT_ID = 2010 -- 指定父节点
    UNION ALL
    SELECT c.ID, c.PARENT_ID FROM NODES c JOIN recursive_nodes p ON c.PARENT_ID = p.ID
)
SELECT * FROM recursive_nodes ORDER BY PARENT_ID, ID;

2. 以Python为例，把查询结果转换成目标JSON：

import json

# 假设从数据库拿到的节点数据
nodes = [
    {"ID": 2007, "PARENT_ID": 2010},
    {"ID": 2008, "PARENT_ID": 2010},
    {"ID": 2009, "PARENT_ID": 2010},
    {"ID": 2002, "PARENT_ID": 2009},
    {"ID": 2003, "PARENT_ID": 2009}
]

def build_tree(parent_id):
    tree = []
    # 找到当前父节点的所有子节点
    children = [n for n in nodes if n["PARENT_ID"] == parent_id]
    for child in children:
        # 递归构建子节点的树
        child_tree = build_tree(child["ID"])
        tree.append({str(child["ID"]): child_tree})
    return tree

# 生成节点2010的层级结构
result = build_tree(2010)
print(json.dumps(result, indent=2))

运行这段代码会输出你想要的JSON格式。

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