0

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.

Dula
  • 1,404
  • 1
  • 14
  • 29

1 Answers1

5

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;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Right, but this includes the parents. I only want the immediate children without the parents in the return value. – Dula Sep 08 '21 at 15:15
  • Ah, I think I understand. See my extended answer. – Laurenz Albe Sep 08 '21 at 15:39
  • 1
    I think this still doesn't work as I expect to and I might've misled you by *{1}. I thought *{1} means return only the next label which is incorrect. It means only match paths that only has 1 more label. What I need is, given 'A.B', is ['C', 'D'] but 'D' here is from both id's 4 AND 5. But since 'D' is duplicate they are returned as a single element. – Dula Sep 20 '21 at 15:52
  • 1
    I think it'll be clear if we add another row | 7 | A.B.G.H. |. Now for 'A.B', the returned list should be [C,D,G]. C from id 3. D from both id's 4 & 5. G from id 7. – Dula Sep 20 '21 at 15:54
  • I have added another query. – Laurenz Albe Sep 21 '21 at 11:04
  • Laurenz, what's the difference between what you have for the SELECT vs `SELECT DISTINCT subpath(name, nlevel('A.B'), 1)`? Yours tells Postgres to start looking after A.B while mine says start from A but gives an offset right? Since I have 1 less subpath() call would it be faster? – Dula Sep 21 '21 at 16:55
  • Your query looks more complicated to me, but I guess that the performance will be about the same. There is nothing wrong with your query. – Laurenz Albe Sep 21 '21 at 21:02