PostgreSQL中如何按LTREE字段全路径段过滤查询指定人员母系长辈

场景说明 #

存在名为people的数据表，包含2个字段：

• name：字符串类型，存储人员姓名
• mothers_hierachy：ltree类型，存储母系亲属层级路径
  表内示例数据：

name    | mothers_hierachy
--------|-------------------
"josef" | "maria.jenny.lisa"

需求：查询josef的所有母系长辈，希望查询结构和IN子查询的写法逻辑相近。

可直接运行的查询语句 #

ltree类型存储的路径是按.拼接的层级标签，不能直接作为IN的匹配集合，需要先拆分路径为独立标签值，和你预期结构一致的查询写法如下：

SELECT * FROM people WHERE name IN (
    SELECT unnest(string_to_array(mothers_hierachy::text, '.'))
    FROM people WHERE name = 'josef'
);

逻辑说明 #

1. 内层子查询先定位到name为josef的记录，将他的ltree类型母系路径转为文本格式
2. 用string_to_array按.分隔符把路径字符串拆分为人名数组，再通过unnest将数组展开为多行独立的人名值，作为IN的匹配范围
3. 外层查询匹配name在这个范围内的所有记录，就能得到josef的全部母系长辈。

如果要使用ltree原生特性提升查询性能，也可以用层级匹配的写法，避免字符串拆分操作：

WITH target AS (
    SELECT mothers_hierachy AS josef_path FROM people WHERE name = 'josef'
)
SELECT p.* FROM people p, target t
WHERE index(t.josef_path::text, p.name) > 0;

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