如何从Postgres的ltree类型给定路径中查询直接子节点

最优实现（支持索引加速） #

推荐使用ltree原生的包含运算符配合层级判断实现，性能远高于正则匹配，适合数据量较大的场景：

SELECT DISTINCT subpath(path, nlevel('A.B'), 1) AS direct_child
FROM test_ltree
WHERE path <@ 'A.B' -- 限定路径属于A.B的分支，可命中path列的GIST索引
  AND nlevel(path) = nlevel('A.B') + 1; -- 仅保留比父路径多一层的直接子节点

逻辑说明： #

• nlevel('A.B') 会返回父路径的层级数，示例中A.B为2层，返回值为2
• ltree的路径下标从0开始计数，subpath(path, 2, 1)表示从下标为2的位置开始截取1段路径，刚好是直接子节点的内容，不会包含父级部分
• <@是ltree的后代包含运算符，筛选所有属于A.B分支的路径，配合提前建好的GIST索引可以大幅提升查询效率
• 层级判断nlevel(path) = nlevel('A.B') + 1确保只返回直接子节点，不会误返回孙子辈及更深的后代节点

正则写法优化 #

如果你更习惯用正则匹配的写法，可以简化你原有的SQL，去掉不必要的嵌套子查询：

SELECT DISTINCT subpath(path, nlevel('A.B'), 1)
FROM test_ltree
WHERE path ~ 'A.B.*{1}';

注意：原写法中的正则A.B.*{1,}会匹配所有至少1层的后代节点，查询时会扫描更多不必要的行，修改为A.B.*{1}可限定仅匹配多1层的直接子节点。

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