PostgreSQL ltree：如何用单查询获取搜索结果的祖先及直接子节点

完全可以用单条PostgreSQL查询实现需求，核心是利用CTE（公共表表达式）结合ltree的路径匹配能力，一次性筛选出所有需要的节点：

WITH target_nodes AS (
    -- 先定位所有符合搜索条件的目标节点
    SELECT uid, path
    FROM tree
    WHERE label ILIKE '%chris%'
),
ancestors AS (
    -- 获取所有目标节点的祖先（含目标节点自身）
    SELECT DISTINCT t.uid, t.path, t.parent, t.label
    FROM tree t
    JOIN target_nodes tn ON t.path @> tn.path
),
ancestor_children AS (
    -- 获取所有祖先节点的直接子节点
    SELECT t.uid, t.path, t.parent, t.label
    FROM tree t
    JOIN ancestors a ON t.parent = a.uid
)
-- 合并结果并按路径排序，保证树形层级逻辑
SELECT * FROM ancestors
UNION
SELECT * FROM ancestor_children
ORDER BY path;

关键逻辑说明： #

• target_nodes：先筛选出所有label包含"chris"的节点，拿到它们的唯一标识和ltree路径，作为后续查询的起点。
• ancestors：通过ltree的@>操作符（表示路径包含关系），匹配出所有目标节点的祖先节点（包括目标节点自己），用DISTINCT避免同一祖先被多次匹配。
• ancestor_children：关联祖先节点，找到所有直接父节点属于祖先集合的子节点，也就是需求中要求的“祖先节点的直接子节点”。
• 最后用UNION合并两个结果集，自动去重（比如目标节点本身既是祖先，也是某个父节点的直接子节点），并通过ORDER BY path保证节点按树形层级顺序排列，方便后续构建树形图。

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