I've got a table in postgres 9.3.5 that looks like this:
CREATE TABLE customer_area_node
(
id bigserial NOT NULL,
customer_id integer NOT NULL,
parent_id bigint,
name text,
description text,
CONSTRAINT customer_area_node_pkey PRIMARY KEY (id)
)
I query with:
WITH RECURSIVE c AS (
SELECT *, 0 as level, name as path FROM customer_area_node WHERE customer_id = 2 and parent_id is null
UNION ALL
SELECT customer_area_node.*,
c.level + 1 as level,
c.path || '/' || customer_area_node.name as path
FROM customer_area_node
join c ON customer_area_node.parent_id = c.id
)
SELECT * FROM c ORDER BY path;
this seems to work to build paths like building1/floor1/room1, building1/floor1/room2, etc.
What I'd like to be able to do is easily turn that into either json that represents the tree structure which I've been told I can do with row_to_json.
As a reasonable alternative, any other way I can format the data to a more efficient mechanism such that I can actually easily turn it into an actual tree structure without having a ton of string.splits on /.
Is there a reasonably easy way to do this with row_to_json?
Sorry for the very late answer but i think i found an elegant solution that could become an accepted answer for this question.
Based on the awesome "little hack" found by @pozs, i came up with a solution that:
NOT EXISTS
predicate)WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS (
-- tree leaves (no matching children)
SELECT c.*, json '[]'
FROM customer_area_node c
WHERE NOT EXISTS(SELECT * FROM customer_area_node AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)
UNION ALL
-- pozs's awesome "little hack"
SELECT (parent).*, json_agg(child) AS "children"
FROM (
SELECT parent, child
FROM customer_area_tree AS child
JOIN customer_area_node parent ON parent.id = child.parent_id
) branch
GROUP BY branch.parent
)
SELECT json_agg(t)
FROM customer_area_tree t
LEFT JOIN customer_area_node AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id IS NULL
Update:
Tested with very simple data, it does work, but as posz pointed out in a comment, with his sample data, some rogue leaf nodes are forgotten. But, i found out that with even more complex data, the previous answer is not working either, because only rogue leaf nodes having a common ancestor with "max level" leaf nodes are caught (when "1.2.5.8" is not there, "1.2.4" and "1.2.5" are absent because they have no common ancestor with any "max level" leaf node).
So here is a new proposition, mixing posz's work with mine by extracting the NOT EXISTS
subrequest and making it an internal UNION
, leveraging UNION
de-duplication abilities (leveraging jsonb comparison abilities):
<!-- 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 on http://rextester.com/SMM38494 ;)