I need to represent a document structure as a JSON object to be passed to a web application to form a hierarchical tree. Of course the order of the components needs to be correct so I am searching for a way to order the parents and children. So every child should be ordered by child_order asc
How could I do that?
Here's the DDL:
CREATE TABLE documents_structure (
id INTEGER NOT NULL
, title CHARACTER VARYING(255) NOT NULL
, parent_id INTEGER
, child_order INTEGER NOT NULL
);
The Insert queries:
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (1,'Root of the document', NULL,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (2,'First Child of root', 1,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (3,'Second Child of root', 1,2);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (4,'Third Child of root', 1,3);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (5,'First Child of Branch 1', 2,1);
INSERT INTO documents_structure (id,title,parent_id,child_order) VALUES (6,'Second Child of Branch 1', 2,2);
And the query to retrieve the JSON hierarchy:
WITH RECURSIVE document_tree("id", "title", "parent_id", "child_order", "children") AS (
-- tree leaves (no matching children)
SELECT c.id,c.title,c.parent_id,c.child_order, json '[]'
FROM documents_structure c
WHERE NOT EXISTS(SELECT id,title,parent_id,child_order FROM documents_structure AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)
UNION ALL
SELECT (parent).id,(parent).title,(parent).parent_id,(parent).child_order, json_agg(child) AS "children"
FROM (
SELECT parent, child
FROM document_tree AS child
JOIN documents_structure parent ON parent.id = child.parent_id
) branch
GROUP BY branch.parent
)
SELECT jsonb_pretty(json_agg(t)::jsonb)
FROM document_tree t
LEFT JOIN documents_structure AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id = 1;
For your convenience I have made a DB fiddle
