generate_json_tree.sql

generate_json_tree.sql

drop type if exists node_tree cascade;

create type node_tree as
    (
        id int,
        label varchar,
        level int,
        children jsonb
    );

CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS
$$
declare
    node              node_tree;
    tree              jsonb = '[]';
    current_level     int   := 0;
    item_level        int   := 0;
    current_path      int[];
    path_without_root int[];
    path_as_array     varchar[];
    path_in_array     varchar;
    current_index     int   := 0;
begin
    FOR node IN
        SELECT id, label, nlevel(path) as level, jsonb_build_array() as children FROM nodes ORDER BY path, label
        LOOP
            item_level := node.level;

            if (item_level > current_level) then
                -- As we go up, we push the current index in our path tracker
                current_path = current_path || current_index;
                -- and... we reset the current index, as we start on a new level with 0
                current_index := 0;
            end if;

            if (item_level < current_level) then
                -- We get it properly sorted with the materialized path, but still have to correctly loop down the right level
                -- As the current subtree might be a lot larger then the next one.
                while (array_length(current_path) > item_level) loop
                -- As we go now a level down, we restore the current index to the last one
                -- before we went up
                current_index := current_path[array_upper(current_path, 1)];
                -- And the current path is now without the last element (array.pop())
                current_path := (select current_path[1:array_upper(current_path, 1) - 1]);
                end loop;
            end if;

            -- If we are on the root level
            if (item_level = 1) then
                tree := tree || to_jsonb(node);

            else

                path_without_root := (select current_path[2:]);
                path_in_array := (
                    select regexp_replace(array_to_string(array_agg(el - 1)::varchar[], '.'), '\.', '.children.', 'g')
                                  from unnest(path_without_root) as el);
                path_as_array := string_to_array(path_in_array, '.') || ARRAY ['children'] || ARRAY ['-1'];
                tree := jsonb_insert(tree, path_as_array,
                                     to_jsonb(node),
                                     true);
            end if;
            current_level := item_level;
            current_index := current_index + 1;
        end loop;

    return tree;
end;
$$
    LANGUAGE plpgsql
    stable;