[SOLVED] How to find the first partial match node above a child node in an LTREE

How to find the first partial match node above a child node in an LTREE

I have a postgres database. In it is a table called structure.

In the structure table is an ltree field called path. It holds data like the following

f55219.p55468.f55635.f44939.p10900.f45675.f10902

f55219.f55468.p43355.f55340

f55219.f55468.f55635.f44939.p11153.f46590.f11222

f55219.p55468.f43355.f48299.f11620

I'm trying to work out how I query the structure table to find the first p* node from the right hand end, and ideally return that portion of the path. i.e. for the first row, I'd love to get back f55219.p55468.f55635.f44939.p10900.

I've tried things like

select 
  index ( ST.path, text2ltree('p*'), -1 ) 
  ,*
from 
  structure ST

which throws an error (and even I could make it work, would only return an index value).

In case it matters, there can be any number (0 - infinity) of f* records before the p* records in the data.

Thanks for the help!

Solution

If I have understood your question correctly then what you are looking for is if a path is like

f55219.p55468.f55635.f44939.p10900.f45675.f10902

you want

f55219.p55468.f55635.f44939.p10900

Please try:

SELECT
  path,
  subpath(
    path,
    0,
    COALESCE((
      SELECT max(idx)::int
      FROM unnest(string_to_array(path::text, '.')) WITH ORDINALITY AS t(label, idx)
      WHERE label LIKE 'p%'
    ), 0)
  ) AS truncated_path
FROM structure;

path::text converts the ltree to a string.

string_to_array(..., '.') splits the path into labels.

Hope it helps.