I have a nested JSON object database query that is used as input for a treeview component. This query works when there is 1 root in the database, but it breaks when having multiple roots. I can't really find a solution to it, could someone take a look?
Link to the dbfiddle: https://dbfiddle.uk/sjYamgm1
Query
CREATE OR REPLACE FUNCTION json_tree2() RETURNS jsonb AS $$
DECLARE
_json_output jsonb;
_temprow record;
BEGIN
SELECT
jsonb_build_object('id', id, 'label', "categoryName", 'children', array_to_json(ARRAY[]::uuid[]))
INTO _json_output
FROM "Category"
WHERE "parentCategory" IS NULL;
FOR _temprow IN
WITH RECURSIVE tree(id, ancestor, child, path, json) AS (
SELECT
t1.id,
NULL::uuid,
t2.id,
'{children}'::text[] || (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
jsonb_build_object('id', t2.id, 'label', t2."categoryName", 'children', array_to_json(ARRAY[]::uuid[]))
FROM "Category" t1
LEFT JOIN "Category" t2 ON t1.id = t2."parentCategory"
WHERE t1."parentCategory" IS NULL
UNION
SELECT
t1.id,
t1."parentCategory",
t2.id,
tree.path || '{children}' || (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
jsonb_build_object('id', t2.id, 'label', t2."categoryName", 'children', array_to_json(ARRAY[]::uuid[]))
FROM "Category" t1
LEFT JOIN "Category" t2 ON t1.id = t2."parentCategory"
INNER JOIN tree ON (t1.id = tree.child)
WHERE t1."parentCategory" = tree.id
)
SELECT
child as id, path, json
FROM tree
WHERE child IS NOT NULL ORDER BY path
LOOP
SELECT jsonb_insert(_json_output, _temprow.path, _temprow.json) INTO _json_output;
END LOOP;
RETURN _json_output;
END;
$$ LANGUAGE plpgsql;
SELECT jsonb_pretty(json_tree2())
Problem: I only get the first root.
{
"id": "bfa3fdf8-4672-404e-baf5-0f9098a5705b",
"label": "1",
"children": [
{
"id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b1",
"label": "2.1",
"children": [
{
"id": "903a727f-d94d-44ff-b2f6-a985fd167342",
"label": "1.1.1",
"children": [
]
},
{
"id": "903a727f-d94d-44ff-b2f6-a985fd167321",
"label": "2.1.1",
"children": [
]
}
]
},
{
"id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b7",
"label": "1.1",
"children": [
]
}
]
}
Expected outcome example
[
{
"id": "bfa3fdf8-4672-404e-baf5-0f9098a5705b",
"label": "1",
"children": [
{
"id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b7",
"label": "1.1",
"children": [
{
"id": "903a727f-d94d-44ff-b2f6-a985fd167342",
"label": "1.1.1",
"children": [
]
},
]
},
{
"id": "bfa3fdf8-4672-404e-baf5-0f9098a5705e",
"label": "2",
"children": [
{
"id": "9dfef3df-d67b-4afd-a591-2e9b1c0b21b1",
"label": "2.1",
"children": [
{
"id": "903a727f-d94d-44ff-b2f6-a985fd167321",
"label": "2.1.1",
"children": [
]
},
]
}
]
Dummy data
CREATE TABLE "Category" (
id uuid,
categoryName text,
parentCategory uuid
);
INSERT INTO "Category" VALUES
('bfa3fdf8-4672-404e-baf5-0f9098a5705b', '1', NULL),
('bfa3fdf8-4672-404e-baf5-0f9098a5705e', '2', NULL),
('9dfef3df-d67b-4afd-a591-2e9b1c0b21b7', '1.1', 'bfa3fdf8-4672-404e-baf5-0f9098a5705b'),
('9dfef3df-d67b-4afd-a591-2e9b1c0b21b1', '2.1', 'bfa3fdf8-4672-404e-baf5-0f9098a5705e'),
('903a727f-d94d-44ff-b2f6-a985fd167342', '1.1.1', '9dfef3df-d67b-4afd-a591-2e9b1c0b21b7'),
('903a727f-d94d-44ff-b2f6-a985fd167321', '2.1.1', '9dfef3df-d67b-4afd-a591-2e9b1c0b21b1');
SELECT * FROM "Category";
In your recursive cte, you can build the tree from the bottom up, starting with the leaf nodes and aggregating at each iteration:
create or replace function json_tree2() returns jsonb AS $$
declare
_json_output jsonb;
_temprow record;
begin
with recursive leaf_normalized(id, cnt, p, o, r) as(
select c.id||1::text||'-NULL', 1, c.id, c.id, (select max(array_length(regexp_split_to_array(c3.categoryName, '\.'), 1)) from category c3) - array_length(regexp_split_to_array(c.categoryName, '\.'), 1) - 1
from category c
where not exists (select 1 from category c1 where c1.parentCategory = c.id) and array_length(regexp_split_to_array(c.categoryName, '\.'), 1) < (select max(array_length(regexp_split_to_array(c3.categoryName, '\.'), 1)) from category c3)
union all
select c.o||(c.cnt + 1)::text||'-NULL', c.cnt + 1, c.id, c.o, c.r - 1 from leaf_normalized c where c.r > 0
),full_normalized(id, categoryName, parentCategory) as (
select c.* from category c
union all
select c.id, '0', c.p from leaf_normalized c
),
cte(id, cname, p, js) as (
select c.id, c.categoryName, c.parentCategory, '[]'::jsonb from full_normalized c
where not exists (select 1 from full_normalized c1 where c1.parentCategory = c.id)
union all
select t.id, t.cname, t.p, jsonb_agg(t.js)
from (select c1.id, c1.categoryName cname, c1.parentCategory p,
jsonb_build_object('id', c.id, 'label', c.cname, 'children', c.js) js
from cte c join full_normalized c1 on c.p = c1.id) t
group by t.id, t.cname, t.p
),
clean(js, f) as (
select (select jsonb_agg(jsonb_build_object('id', c.id, 'label', c.cname, 'children', c.js))
from cte c where c.p is null)::text, 1
union all
select regexp_replace(c.js, '\{"id":\s"[\w\-]+\-NULL", "label"\: "\w+", "children": \[\]\}(?:,\s)*', ''), (array_length(regexp_matches(c.js, '\{"id":\s"[\w\-]+\-NULL", "label"\: "\w+", "children": \[\]\}(?:,\s)*'), 1) > 0)::int from clean c where c.f = 1
)
select (select c.js::jsonb from clean c where not exists (select 1 from regexp_matches(c.js, '\{"id":\s"[\w\-]+\-NULL", "label"\: "\w+", "children": \[\]\}(?:,\s)*')))
into _json_output;
return _json_output;
end;
$$ language plpgsql;