PostgreSQL基于ltree实现多层子孙节点嵌套为查询列的问题
问题根因梳理
你之前的SQL实现达不到预期,核心有三个问题:
- 顶层节点筛选逻辑缺失:没有单独判定哪些是真的顶层记录,把所有表内的行都当成返回的根节点,自然把已经是子级的Record2、Record3也单独列在了结果里
- 无递归嵌套逻辑:之前的子查询只是把当前节点下的所有后代标题平铺成一维列表,没有把每个子节点自身携带的孙级嵌套进去,永远只能输出1层结构
- 数据类型不匹配:普通SQL的一维数组本身存不了多层嵌套的内容,必须用支持嵌套结构的类型(比如
jsonb)才能承载你要的多层树结构
另外你写的SQL里还有一处笔误:把子查询的表别名t错写成了At,会直接触发语法报错。
实现方案
核心思路
- 用PostgreSQL的递归CTE(
WITH RECURSIVE语法)逐层级组装嵌套结构:从最末端的叶子节点(没有任何后代的节点)开始,逐层向上把每个节点的直接子节点组装成自身的children字段,直到拼完所有层级 - 最后筛选出所有真正的顶层节点(没有任何父级存在的记录)作为最终返回结果,已经被嵌套进父级结构的子节点不会单独出现在结果集里
- 用
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
相关产品推荐
相关产品推荐

