sqlpostgresqlltree

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.