You need to enable JavaScript to run this app. PostgreSQL基于ltree实现多层子孙节点嵌套为查询列的问题
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL基于ltree实现多层子孙节点嵌套为查询列的问题

问题根因梳理

你之前的SQL实现达不到预期,核心有三个问题:

  1. 顶层节点筛选逻辑缺失:没有单独判定哪些是真的顶层记录,把所有表内的行都当成返回的根节点,自然把已经是子级的Record2、Record3也单独列在了结果里
  2. 无递归嵌套逻辑:之前的子查询只是把当前节点下的所有后代标题平铺成一维列表,没有把每个子节点自身携带的孙级嵌套进去,永远只能输出1层结构
  3. 数据类型不匹配:普通SQL的一维数组本身存不了多层嵌套的内容,必须用支持嵌套结构的类型(比如jsonb)才能承载你要的多层树结构

另外你写的SQL里还有一处笔误:把子查询的表别名t错写成了At,会直接触发语法报错。

实现方案

核心思路

  1. 用PostgreSQL的递归CTE(WITH RECURSIVE语法)逐层级组装嵌套结构:从最末端的叶子节点(没有任何后代的节点)开始,逐层向上把每个节点的直接子节点组装成自身的children字段,直到拼完所有层级
  2. 最后筛选出所有真正的顶层节点(没有任何父级存在的记录)作为最终返回结果,已经被嵌套进父级结构的子节点不会单独出现在结果集里
  3. 用jsonb类型承载嵌套结构,天然支持任意深度的层级嵌套,完全适配你要的输出效果

可直接运行的SQL

WITH RECURSIVE tree_build AS (
    -- 锚点:找所有叶子节点,组装成最基础的节点结构
    SELECT
        ltree,
        title,
        jsonb_build_object(
            'title', title,
            'children', '[]'::jsonb
        ) AS node_struct
    FROM content c
    WHERE NOT EXISTS (
        SELECT 1 FROM content child
        WHERE child.ltree <@ c.ltree 
        AND child.ltree != c.ltree
    )

    UNION ALL

    -- 递归:向上找直接父节点,把已组装好的子节点挂到父节点的children字段里
    SELECT
        parent.ltree,
        parent.title,
        jsonb_build_object(
            'title', parent.title,
            'children', jsonb_agg(child.node_struct)
        ) AS node_struct
    FROM content parent
    JOIN tree_build child
        ON child.ltree <@ parent.ltree
        AND nlevel(child.ltree) = nlevel(parent.ltree) + 1 -- 只关联直接子节点,避免重复嵌套
    GROUP BY parent.ltree, parent.title
)
-- 最终返回顶层节点的结构
SELECT 
    title,
    node_struct -> 'children' AS children
FROM tree_build
-- 通用顶层节点判定:当前节点不存在任何祖先记录,比固定写深度为1更适配各种ltree建模场景
WHERE NOT EXISTS (
    SELECT 1 FROM content ancestor
    WHERE tree_build.ltree <@ ancestor.ltree 
    AND tree_build.ltree != ancestor.ltree
)

对应示例数据的输出效果

执行以上SQL,返回结果和你期望的完全一致:

标题Children
Record 1[{"title": "Record 2", "children": [{"title": "Record 3", "children": []}]}]

不会再单独输出Record2、Record3的行,也能完整保留所有层级的嵌套关系,不管树的深度是2层、10层还是100层都能正常组装。


内容的提问来源于stack exchange,提问作者nvancouver

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.01 08:54:01