在PostgreSQL中从树形结构生成嵌套JSON对象

1. 假设表结构（基于常见关联场景） #

先明确核心表结构，若你的表结构有差异，可对应调整字段关联逻辑：

• groups 表（存储文件夹）：

  CREATE TABLE groups (
      id INT PRIMARY KEY,
      parent_id INT REFERENCES groups(id), -- 父文件夹ID，顶级为NULL
      name VARCHAR(100) NOT NULL
  );
  

• layers 表（存储图层）：

  CREATE TABLE layers (
      id INT PRIMARY KEY,
      group_id INT REFERENCES groups(id), -- 所属文件夹ID，可为NULL表示顶级图层
      name VARCHAR(100) NOT NULL
  );
  

2. 完整SQL实现 #

通过递归CTE分层构建文件夹树形，同时关联对应图层，确保子文件夹和图层分属不同JSON字段：

WITH RECURSIVE group_tree AS (
    -- 递归起点：顶级文件夹
    SELECT
        g.id,
        g.name,
        g.parent_id,
        -- 提取当前文件夹下的所有图层
        COALESCE(
            json_agg(json_build_object('id', l.id, 'name', l.name)) FILTER (WHERE l.id IS NOT NULL),
            '[]'::json
        ) AS layers
    FROM groups g
    LEFT JOIN layers l ON g.id = l.group_id
    WHERE g.parent_id IS NULL
    GROUP BY g.id, g.name, g.parent_id

    UNION ALL

    -- 递归遍历子文件夹
    SELECT
        g.id,
        g.name,
        g.parent_id,
        COALESCE(
            json_agg(json_build_object('id', l.id, 'name', l.name)) FILTER (WHERE l.id IS NOT NULL),
            '[]'::json
        ) AS layers
    FROM groups g
    LEFT JOIN layers l ON g.id = l.group_id
    JOIN group_tree gt ON g.parent_id = gt.id
    GROUP BY g.id, g.name, g.parent_id
),
-- 组装完整嵌套结构
final_tree AS (
    SELECT
        json_build_object(
            'name', gt.name,
            'groups', COALESCE(json_agg(ft.node), '[]'::json), -- 嵌套子文件夹
            'layers', gt.layers -- 关联当前文件夹的图层
        ) AS node,
        gt.id
    FROM group_tree gt
    LEFT JOIN final_tree ft ON gt.id = ft.parent_id
    WHERE gt.parent_id IS NULL
    GROUP BY gt.id, gt.name, gt.layers
)
SELECT json_agg(node) AS full_tree FROM final_tree;

3. 关键逻辑说明 #

• 递归分层隔离：group_tree CTE先从顶级文件夹开始，逐层获取子文件夹及其绑定的图层，避免文件夹与图层的逻辑混淆；
• 双数组区分结构：最终组装时明确将子文件夹放入groups数组，图层放入layers数组，彻底解决子文件夹混入图层列表的问题；
• 空值处理：用COALESCE确保无内容时返回空数组，保证JSON结构的合法性。

4. 测试示例 #

插入测试数据：

-- 文件夹
INSERT INTO groups VALUES (1, NULL, '根目录');
INSERT INTO groups VALUES (2, 1, '地图图层组');
INSERT INTO groups VALUES (3, 2, '卫星影像组');
-- 图层
INSERT INTO layers VALUES (101, 1, '全局底图');
INSERT INTO layers VALUES (102, 2, '行政区划');
INSERT INTO layers VALUES (103, 3, '2024年卫星图');

执行SQL后生成的JSON结构：

[
  {
    "name": "根目录",
    "groups": [
      {
        "name": "地图图层组",
        "groups": [
          {
            "name": "卫星影像组",
            "groups": [],
            "layers": [{"id": 103, "name": "2024年卫星图"}]
          }
        ],
        "layers": [{"id": 102, "name": "行政区划"}]
      }
    ],
    "layers": [{"id": 101, "name": "全局底图"}]
  }
]

5. 原有SQL的修正方向 #

若你的原有代码出现子文件夹混入图层的问题，通常是两个原因：

• 未区分文件夹与图层的关联逻辑，错误将子文件夹的查询结果合并到图层集合；
• 递归组装时未单独为子文件夹开辟groups字段，而是直接混入layers数组。

修正时只需确保：递归过程中分别提取当前节点的子文件夹集合和图层集合，最终组装时将两个集合分别放入对应JSON字段即可。

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