postgresqlltree

Get immediate children from Postgres ltree path


How do I get the immediate children from a given path in an ltree type in Postgres assuming the path always starts at root? For example if the given path is A.B and the column name of ltree is path, then the best I could come up with is:

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

Edit: I only want to return the children, without the parents in the path.


Solution

  • That should be trivial:

    TABLE ltree;
    
     id │  path   
    ════╪═════════
      1 │ A
      2 │ A.B
      3 │ A.B.C
      4 │ A.B.D
      5 │ A.B.D.E
      6 │ A.F
    (6 rows)
    
    SELECT * FROM ltree
    WHERE path ~ 'A.B.*{1}'::lquery;
    
     id │ path  
    ════╪═══════
      3 │ A.B.C
      4 │ A.B.D
    (2 rows)
    

    The query finds all immediate children of A.B.

    If you want to omit the A.B from the result, use subpath with a negative offset:

    SELECT subpath(path, -1)
    FROM ltree
    WHERE path ~ 'A.B.*{1}'::lquery;
    
     subpath 
    ═════════
     C
     D
    (2 rows)
    

    To get only the next label after A.B, you could try

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