This is tricky.
It's a recursive problem, but standard recursive CTEs are ill-equipped to deal with it, as we need to aggregate on every level and CTEs do not allow aggregation in the recursive term.
I solved it with a PL/pgSQL function:
CREATE OR REPLACE FUNCTION f_build_jsonb_tree(_type text = NULL)
RETURNS jsonb
LANGUAGE plpgsql AS
$func$
DECLARE
_nest_lvl int;
BEGIN
-- add level of nesting recursively
CREATE TEMP TABLE t ON COMMIT DROP AS
WITH RECURSIVE t AS (
SELECT *, 1 AS lvl
FROM account
WHERE "parentId" IS NULL
AND (type = _type OR _type IS NULL) -- default: whole table
UNION ALL
SELECT a.*, lvl + 1
FROM t
JOIN account a ON a."parentId" = t.id
)
TABLE t;
-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON t (lvl, id);
_nest_lvl := (SELECT max(lvl) FROM t);
-- no nesting found, return simple result
IF _nest_lvl = 1 THEN
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg(sub) AS accounts
FROM (
SELECT id, code, type, "parentId", NULL AS children
FROM t
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END IF;
-- start collapsing with leaves at highest level
CREATE TEMP TABLE j ON COMMIT DROP AS
SELECT "parentId" AS id
, jsonb_agg (sub) AS children
FROM (
SELECT id, code, type, "parentId" -- type redundant?
FROM t
WHERE lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";
-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON j (id);
-- iterate all the way down to lvl 2
-- write to same table; ID is enough to identify
WHILE _nest_lvl > 2
LOOP
_nest_lvl := _nest_lvl - 1;
INSERT INTO j(id, children)
SELECT "parentId" -- AS id
, jsonb_agg(sub) -- AS children
FROM (
SELECT id, t.code, t.type, "parentId", j.children -- type redundant?
FROM t
LEFT JOIN j USING (id) -- may or may not have children
WHERE t.lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";
END LOOP;
-- nesting found, return nested result
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg (sub) AS accounts
FROM (
SELECT id, code, type, "parentId", j.children
FROM t
LEFT JOIN j USING (id)
WHERE t.lvl = 1
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END
$func$;
Call (returns desired result exactly):
SELECT jsonb_pretty(f_build_jsonb_tree());
db<>fiddle here - with extended test case
I chose the key name children instead of child, as multiple may be nested.
jsonb_pretty() to prettify the display is optional.
This is assuming referential integrity; should be implemented with a FK constraint.
The solution might be simpler for your particular case, utilizing the code column - if it exhibits (undisclosed) useful properties. Like we might derive the nesting level without rCTE and added temporary table t. But I am aiming for a general solution based on ID references only.
There is a lot going on in the function. I added inline comments. Basically, it does this:
- Create a temporary table with added nesting level (
lvl)
- If no nesting is found, return simple result
- If nesting is found, collapse to
jsonb from the top nesting level down.
Write all intermediary results to a second temp table j.
- Once we reach the second nesting level, return full result.
The function takes _type as parameter to return only the given type. Else, the whole table is processed.
Aside: avoid mixed-case identifiers like "parentId" in Postgres if at all possible. See:
Related later answer using a recursive function:
