Sorry for the very late answer, but I think I found an elegant solution that could be the accepted answer to this question.
Based on the amazing βlittle hackβ found by @pozs, I came up with a solution that:
- solves the problem of "rogue" with a very small code (using the predicate
NOT EXISTS ) - avoids the entire calculation process / state of the entire level
WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS (
Update
Tested with very simple data , it really works, but, as posz noted in the comment, with data samples , some nodes of the rogue leaves are forgotten. But I found out that with even more complex data , the previous answer also does not work, because only nodes of rogue leaflets having a common ancestor with "nodes" of the maximum level "are caught (when" 1.2.5.8 "does not exist," 1.2.4 " and "1.2.5" are absent because they do not have a common ancestor with any leaf of the "maximum level" node).
So here is a new sentence mixing posz to work with mine, extracting the NOT EXISTS subquery and making it an internal UNION , using the duplicate UNION removal options (using the jsonb comparison capabilities):
<!-- language: sql --> WITH RECURSIVE c_with_level AS ( SELECT *, 0 as lvl FROM customer_area_node WHERE parent_id IS NULL UNION ALL SELECT child.*, parent.lvl + 1 FROM customer_area_node child JOIN c_with_level parent ON parent.id = child.parent_id ), maxlvl AS ( SELECT max(lvl) maxlvl FROM c_with_level ), c_tree AS ( SELECT c_with_level.*, jsonb '[]' children FROM c_with_level, maxlvl WHERE lvl = maxlvl UNION ( SELECT (branch_parent).*, jsonb_agg(branch_child) FROM ( SELECT branch_parent, branch_child FROM c_with_level branch_parent JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id ) branch GROUP BY branch.branch_parent UNION SELECT c.*, jsonb '[]' children FROM c_with_level c WHERE NOT EXISTS (SELECT 1 FROM c_with_level hypothetical_child WHERE hypothetical_child.parent_id = c.id) ) ) SELECT jsonb_pretty(row_to_json(c_tree)::jsonb) FROM c_tree WHERE lvl = 0;
Tested at http://rextester.com/SMM38494 ;)