如何在PostgreSQL的LTREE字段中查找从右数首个p*节点并返回对应路径段

问题场景 #

我有一个PostgreSQL数据库，其中structure表的path字段是ltree类型，存储的路径示例如下：

f55219.p55468.f55635.f44939.p10900.f45675.f10902
f55219.f55468.p43355.f55340
f55219.f55468.f55635.f44939.p11153.f46590.f11222
f55219.p55468.f43355.f48299.f11620

需要查询表，找到路径右端开始的第一个p*节点，并返回该节点及其之前的路径部分。比如第一行期望返回f55219.p55468.f55635.f44939.p10900。

之前尝试的index(ST.path, text2ltree('p*'), -1)报错，且只能返回索引值，无法得到目标路径。

解决方案 #

方案1：递归遍历ltree节点（推荐） #

利用递归CTE直接操作ltree类型，从路径的最后一个节点向上遍历，直到找到第一个以p开头的节点，再截取对应路径。这种方法避免字符串转换开销，更高效可靠。

WITH recursive path_traversal AS (
  SELECT 
    path,
    nlevel(path) AS current_level,
    subpath(path, nlevel(path) - 1, 1) AS current_node
  FROM structure
  UNION ALL
  SELECT
    pt.path,
    pt.current_level - 1,
    subpath(pt.path, pt.current_level - 2, 1)
  FROM path_traversal pt
  WHERE pt.current_level > 1 AND pt.current_node !~ '^p'
)
SELECT 
  subpath(path, 0, current_level) AS target_path,
  path AS original_path
FROM path_traversal
WHERE current_node ~ '^p'
ORDER BY original_path, current_level DESC
LIMIT ALL;

方案2：字符串转换结合数组操作 #

如果更习惯字符串处理，可以将ltree转成文本后分割为数组，从后往前定位第一个p*元素的位置，再截取对应路径：

SELECT
  subpath(
    path,
    0,
    array_position(
      string_to_array(text(path), '.'),
      (
        SELECT elem
        FROM unnest(string_to_array(text(path), '.')) WITH ORDINALITY AS t(elem, pos)
        WHERE elem LIKE 'p%'
        ORDER BY pos DESC
        LIMIT 1
      )
    )
  ) AS target_path,
  path AS original_path
FROM structure;

结果验证 #

对于示例数据，两种方法都会返回如下结果：

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