Home > OS >  Nested JSON object query only returns on first root
Nested JSON object query only returns on first root

Time:12-02

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";

CodePudding user response:

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;

See fiddle.

  • Related