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.
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;