如何从PostgreSQL表生成含节点ID的JSON树并创建视图

需求说明 #

需要将存储层级数据的PostgreSQL表，按section字段分组构建嵌套JSON树结构：

• 第一层为section节点，包含对应id和section名称，嵌套subsections数组
• 第二层为subsection节点，包含对应id和subsection名称，嵌套subsections数组（对应原表的subsubsection）
• 第三层为subsubsection节点，包含对应id和subsubsection名称
• 最终结果保存为视图

表结构 #

期望JSON结构示例 #

{
  "id": 111,   
  "section": "s_1",    
  "subsections": [
    {
      "id": 222,
      "subsection": "ss_2",
      "subsections": [
        {
          "id": 333,
          "subsection": "sss_3"
        },
        {
          "id": 444,
          "subsection": "sss_4"
        }
      ]
    }
  ]
}

原尝试代码（缺少ID字段） #

create or replace view my_view(
    name
) as
SELECT 
       (
            SELECT
                json_build_object(
                        'section', a.section,
                        'subsections', a.sections
                )
            FROM (SELECT b.section,
                         json_agg(
                                 json_build_object(
                                         'subsection', b.subsection,
                                         'subsubsections', b.subsections
                                 )
                         ) AS sections
                  FROM (SELECT c.section,
                               c.subsection,
                               json_agg(
                                        json_build_object(
                                                'subsubsection', c.subsubsection
                                        )
                                   ) AS subsections
                        FROM table AS c
                        GROUP BY section, subsection
                       ) AS b
                  GROUP BY b.section) AS a
       ) AS name;

修改后的解决方案代码 #

核心思路是在每个分组层级中，筛选出对应节点的根ID（比如section的根ID是subsection和subsubsection均为null的记录ID），同时在聚合时为每个节点包含自身ID：

CREATE OR REPLACE VIEW my_tree_view AS
SELECT json_agg(section_nodes) AS tree
FROM (
    SELECT
        json_build_object(
            'id', section_root.id,
            'section', t.section,
            'subsections', subsection_nodes
        ) AS section_nodes
    FROM (SELECT DISTINCT section FROM your_table) t
    -- 获取每个section对应的根节点ID
    JOIN your_table section_root
        ON section_root.section = t.section
        AND section_root.subsubsection IS NULL
        AND section_root.subsection IS NULL
    -- 聚合该section下的所有subsection节点
    LEFT JOIN LATERAL (
        SELECT json_agg(subsection_node) AS subsection_nodes
        FROM (
            SELECT
                json_build_object(
                    'id', subsection_root.id,
                    'subsection', s.subsection,
                    'subsections', subsubsection_nodes
                ) AS subsection_node
            FROM (SELECT DISTINCT subsection FROM your_table WHERE section = t.section AND subsection IS NOT NULL) s
            -- 获取每个subsection对应的根节点ID
            JOIN your_table subsection_root
                ON subsection_root.section = t.section
                AND subsection_root.subsection = s.subsection
                AND subsection_root.subsubsection IS NULL
            -- 聚合该subsection下的所有subsubsection节点
            LEFT JOIN LATERAL (
                SELECT json_agg(
                    json_build_object(
                        'id', ss.id,
                        'subsection', ss.subsubsection
                    )
                ) AS subsubsection_nodes
                FROM your_table ss
                WHERE ss.section = t.section
                  AND ss.subsection = s.subsection
                  AND ss.subsubsection IS NOT NULL
            ) ss_nodes ON true
        ) subsections
    ) sub_nodes ON true
) sections;

代码说明 #

1. section层级：

   • 先获取所有唯一的section值
   • 关联表匹配每个section的根节点（subsection和subsubsection都为null的记录），拿到对应ID
   • 通过LATERAL子查询聚合该section下的所有subsection节点

2. subsection层级：

   • 获取当前section下所有非null的subsection值
   • 关联表匹配每个subsection的根节点（subsubsection为null的记录），拿到对应ID
   • 通过LATERAL子查询聚合该subsection下的所有subsubsection节点

3. subsubsection层级：

   • 直接筛选当前section和subsection下的非nullsubsubsection记录，聚合时包含每条记录的ID和名称

注意事项 #

• 将代码中的your_table替换为你实际的表名
• 若存在多个同section的根节点（即多条subsection和subsubsection都为null的记录），需根据业务逻辑调整JOIN条件或使用MAX(id)等聚合函数确保每个section只取一个根ID

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