I have tables that are basically as follows:
CREATE TABLE persons (
person_id serial primary key,
parent_person_id int REFERENCES persons (person_id),
person_name text
);
INSERT INTO persons (person_id,parent_person_id,person_name) VALUES
(1,NULL,'Grandpa'),
(2,1,'Dad'),
(3,2,'Me'),
(4,1,'Uncle'),
(5,4,'Cousin'),
(6,4,'Other Cousin'),
(7,NULL,'Bachelor Great Uncle');
CREATE TABLE details (
detail_id serial primary key,
detail text
);
INSERT INTO details (detail_id,detail) VALUES
(1,'beard'),
(2,'combover'),
(3,'long-windedness'),
(4,'pot-belly');
CREATE TABLE person_details (
person_id int NOT NULL REFERENCES persons (person_id),
detail_id int NOT NULL REFERENCES details (detail_id)
);
INSERT INTO person_details (person_id, detail_id) VALUES
(1,2),
(1,3),
(2,2),
(3,1),
(3,3),
(4,2),
(4,4),
(4,3),
(5,1),
(6,2),
(6,4);
The depth of the hierarchy can be any arbitrary number (i.e., following a parent_person_id up the tree can take you up an unknown number of levels before you hit a person where parent_person_id is NULL). Any number of details (including zero) can be attached to any person. There can be many "top-level" persons (i.e., parent_person_id is NULL), with their own, associated descendant tree.
I would like to get something like the following JSON result, where each "generation" level of the JSON object has the same set of objects, and is nested within it's parent's object.
{
"person ID": 1,
"person Name": 'Grandpa',
"details": [
{"detail": "combover"},
{"detail": "long-windedness"}
],
"descendents": [
{
"person ID": 2,
"person Name": 'Dad',
"details": [
{"detail":"combover"}
]
"descendents": [
{
"person ID": 3,
"person Name": 'Me'
}
]
},
{
"person ID": 4,
"person Name": 'Uncle'
"descendents": [
{
"person ID": 5,
"person Name": "Cousin",
"details"[
"detail": "beard"
]
},
{
"person ID": 6,
"person Name": "Other Cousin",
"details"[
"detail": "combover"
]
},
]
}
]
}
EDIT
I'm getting closer, albeit in sort of a clunky way. I first created a view that assembles the JSON object for each person:
CREATE OR REPLACE VIEW details_by_person AS (
WITH RECURSIVE family_tree AS (
-- top-level persons
SELECT
persons.person_id,
parent_person_id,
1 as depth
FROM persons
WHERE parent_person_id IS NULL
GROUP BY persons.person_id,persons.parent_person_id
UNION ALL
-- Recursive persons
SELECT
t.person_id,
t.parent_person_id,
ft.depth + 1 AS depth
FROM persons t
INNER JOIN family_tree ft ON t.parent_person_id = ft.person_id
GROUP BY t.person_id,t.parent_person_id,ft.depth
)
SELECT
persons.person_id,
person_name AS name,
persons.parent_person_id,
depth,
jsonb_build_object('details',jsonb_agg(detail)) AS details
FROM persons
LEFT OUTER JOIN person_details USING (person_id)
LEFT OUTER JOIN details USING (detail_id)
JOIN family_tree ft ON ft.person_id = persons.person_id
GROUP BY persons.person_id,depth
);
With that done, I created a function that assembles view-creation language that gives me almost the desired result:
CREATE OR REPLACE FUNCTION create_person_details_view()
RETURNS boolean
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
level_depth INT;
view_create_string TEXT;
BEGIN
SELECT max(depth) FROM details_by_person INTO level_depth;
RAISE NOTICE 'Max depth is %',level_depth;
-- Will always need a leaf-node CTE:
SELECT FORMAT('
CREATE OR REPLACE VIEW personz AS
WITH level_%s AS (
SELECT
details_by_person.parent_person_id,
json_build_object(
''id'',person_id,
''name'',name,
''details'',
json_agg(
details
)
) AS person_json
FROM details_by_person
WHERE depth = %s
GROUP BY
person_id,
parent_person_id,
name
)',level_depth,level_depth) INTO view_create_string;
level_depth = level_depth - 1; -- take us from the bottom node to the next one up
WHILE level_depth > 1 LOOP --if > 2 levels max depth, construct next cte w/ next level
view_create_string = view_create_string || FORMAT(',
level_%s AS (SELECT
details_by_person.person_id,
details_by_person.parent_person_id,
json_build_object(
''id'',details_by_person.person_id,
''name'',details_by_person.name,
''details'',json_agg(details_by_person.details),
''children'',json_agg(level_%s.person_json)
)
AS person_json
FROM details_by_person
LEFT OUTER JOIN level_%s ON level_%s.parent_person_id = details_by_person.person_id
WHERE depth = %s
GROUP BY
details_by_person.person_id,
details_by_person.parent_person_id,
details_by_person.name
)',level_depth,level_depth+1,level_depth+1,level_depth+1,level_depth);
RAISE NOTICE 'Current level depth is %',level_depth;
level_depth = level_depth - 1;
END LOOP;
view_create_string = view_create_string || FORMAT('
SELECT
details_by_person.person_id,
details_by_person.parent_person_id,
json_build_object(
''id'',details_by_person.person_id,
''name'',details_by_person.name,
''details'',json_agg(details_by_person.details),
''children'',json_agg(level_%s.person_json)
)
AS person_json
FROM details_by_person
LEFT OUTER JOIN level_%s ON level_%s.parent_person_id = details_by_person.person_id
WHERE depth = %s
GROUP BY
details_by_person.person_id,
details_by_person.parent_person_id,
details_by_person.name;
',level_depth+1,level_depth+1,level_depth+1,level_depth);
--RAISE NOTICE '%',view_create_string;
EXECUTE FORMAT ('%s',view_create_string);
RETURN TRUE;
END;
$BODY$;
I'm not sure whether this approach is clever, or stupid (probably the latter), but after executing the function to create the view, I get rows with a JSON column containing the details for each top-most element, like the example here for one of the top-most elements:
{
"id": 1,
"name": "Grandpa",
"details": [
[
"combover",
"long-windedness"
],
[
"combover",
"long-windedness"
]
],
"children": [
{
"id": 2,
"name": "Dad",
"details": [
[
"combover"
]
],
"children": [
{
"id": 3,
"name": "Me",
"details": [
[
"beard",
"long-windedness"
]
]
}
]
},
{
"id": 4,
"name": "Uncle",
"details": [
[
"combover",
"pot-belly",
"long-windedness"
],
[
"combover",
"pot-belly",
"long-windedness"
]
],
"children": [
{
"id": 5,
"name": "Cousin",
"details": [
[
"beard"
]
]
},
{
"id": 6,
"name": "Other Cousin",
"details": [
[
"combover",
"pot-belly"
]
]
}
]
}
]
}
This is very close to what I want, but if you look closely, you'll see that it's duplicating the "details" object for any person with more than 1 child (e.g., 'Grandpa', 'Uncle').