PostgreSQL：将单表数据转换为分组JSON树形结构

这个需求挺常见的，我给你两种实用的实现思路——一种是在应用层处理（拿Python举例子），另一种是直接用PostgreSQL的JSON函数在数据库端生成结果：

方法一：应用层处理（Python示例） #

如果你的业务逻辑本来就在应用层处理数据，这种方式逻辑直观，灵活性也高。我们可以通过字典快速定位已存在的节点，避免重复创建，逐层构建树形结构：

import psycopg2

# 连接数据库并查询数据（替换成你的数据库信息）
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd host=your_host")
cur = conn.cursor()
cur.execute("SELECT lvl1, lvl2, lvl3, item FROM your_table_name")
rows = cur.fetchall()

# 初始化树形结构的根容器和一级节点映射
tree = []
level1_map = {}

for lvl1, lvl2, lvl3, item in rows:
    # 处理一级节点：不存在则创建并加入树形结构
    if lvl1 not in level1_map:
        level1_node = {"id": lvl1, "children": []}
        level1_map[lvl1] = level1_node
        tree.append(level1_node)
    current_level1 = level1_map[lvl1]
    
    # 处理二级节点：先构建当前一级节点下的二级节点映射，不存在则创建
    level2_map = {node["id"]: node for node in current_level1["children"]}
    if lvl2 not in level2_map:
        level2_node = {"id": lvl2, "children": []}
        level2_map[lvl2] = level2_node
        current_level1["children"].append(level2_node)
    current_level2 = level2_map[lvl2]
    
    # 处理三级节点：构建当前二级节点下的三级节点映射，不存在则创建
    level3_map = {node["id"]: node for node in current_level2["children"]}
    if lvl3 not in level3_map:
        level3_node = {"id": lvl3, "items": []}
        level3_map[lvl3] = level3_node
        current_level2["children"].append(level3_node)
    current_level3 = level3_map[lvl3]
    
    # 将当前item添加到三级节点的items数组
    current_level3["items"].append(item)

# 打印结果（或者返回给业务逻辑）
print(tree)

# 关闭数据库连接
cur.close()
conn.close()

方法二：PostgreSQL数据库端直接生成JSON #

如果想减少应用层的代码量，或者需要直接从数据库拿到最终的JSON结构，可以用PostgreSQL的JSON聚合函数来实现，通过三层嵌套分组完成树形构建：

WITH level3_nodes AS (
    -- 先聚合每个三级节点的item数组，构造三级节点JSON对象
    SELECT
        lvl1,
        lvl2,
        json_build_object(
            'id', lvl3,
            'items', json_agg(item)
        ) AS level3_obj
    FROM your_table_name
    GROUP BY lvl1, lvl2, lvl3
),
level2_nodes AS (
    -- 聚合二级节点下的所有三级节点，构造二级节点JSON对象
    SELECT
        lvl1,
        json_build_object(
            'id', lvl2,
            'children', json_agg(level3_obj)
        ) AS level2_obj
    FROM level3_nodes
    GROUP BY lvl1, lvl2
)
-- 最终聚合一级节点下的所有二级节点，生成完整树形JSON数组
SELECT json_agg(
    json_build_object(
        'id', lvl1,
        'children', json_agg(level2_obj)
    )
) AS tree_structure
FROM level2_nodes
GROUP BY lvl1;

逻辑说明： #

1. level3_nodes：按lvl1、lvl2、lvl3分组，把同一三级节点下的item聚合成数组，用json_build_object构造出三级节点的JSON结构；
2. level2_nodes：按lvl1、lvl2分组，把对应二级节点下的所有三级节点JSON对象聚合成children数组，构造二级节点的JSON结构；
3. 最外层：按lvl1分组，把对应一级节点下的所有二级节点JSON对象聚合成children数组，最终生成完整的树形JSON数组。

两种方法各有优劣：应用层处理更灵活，适合需要额外数据处理逻辑的场景；数据库端处理更高效，能减少应用层与数据库之间的数据传输量，按需选择即可。

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