0

"Content" table has a column "ltree" - Content items are created with a hierarchy based on this ltree.

For example:

Title LTREE
Record 1 1
Record 2 1.2
Record 3 1.2.3

What I'm attempting to do is create a SELECT statement in which I receive a nested response of that records children:

Title Children
Record 1 {Record 2, {Record 3, {}}}

The goal is to retrieve a distinct number of "top level" records, as well as the nested children that are assigned to that top level record through the LTREE

I have attempted:

SELECT DISTINCT title, ARRAY(SELECT title FROM content t WHERE t.ltree <@ content.ltree At.ltree != content.ltree) as children

Which would provide me

Title Children
Record 1 {Record 2}
Record 2 {Record 3}
Record 3 {}

However, this will only provide me with a response "1 layer deep", I have yet to find a way to continue to nest the response - and if that would be possible - to then remove Record 2 and Record 3 from the above select as it is present within one of the nested records.

Any suggestions are much appreciated - I'm not entirely in my element when it comes to psql/sql.

nvancouver
  • 137
  • 11

1 Answers1

1

You can use ARRAY_AGG function to aggregaget a group of inheritance from the current node:

select t.title, (select array_agg(p.ltree) from content p where p.ltree <@ t.ltree) from content t;

This will give you current and all child nodes of the tree like this:

title array_agg
Record 1 {1,1.2,1.2.3}
Record 2 {1.2,1.2.3}
Record 3 {1.2.3}

Since this is a tree structure nesting is kind of against common sense, as the tree is actually a nested structure. If you would like to nest rows I would risk saying you gonna end up working with some sort of a stored procedure, as it will require a multi level sub-select. One can try a hierarchical query, but it's a somewhat complicated task at this point.

Łukasz Rżanek
  • 5,786
  • 27
  • 37