SQL - Split json by reference-CodePudding

I’m implementing decision trees and I would like to create a nested json object that represent trees from nodes accoring to these tables:

prm_decision_trees:

tree
PK character varying

prm_tree_nodes:

id	tree_id	parent_id	question
PK character varying	FK character varying - prm_decision_trees (tree)	FK character varying - prm_tree_nodes (id)	character varying

I already created a function that will make a nested json tree following the solution of: tree_pattern

However, I cannot manage to create a json tree for each tree_id. Here's what I tried so far:

SQL function query:

    CREATE OR REPLACE FUNCTION nested_json_tree() RETURNS table (_json_output jsonb, tree_id 

    character varying(11)) AS $$
    DECLARE
       _json_output jsonb;
       _temprow record;
    BEGIN
       SELECT
        jsonb_build_object('question', question, 'children', array_to_json(ARRAY[]::varchar[]))
        INTO _json_output
        FROM prm_tree_nodes
        WHERE parent_id IS NULL
    
        FOR _temprow IN
           WITH RECURSIVE tree(node_id, ancestor, child, path, nested_json_tree) AS (
            SELECT
               t1.id,
               NULL::VARCHAR,
               t2.id,
               '{children}'::text[] ||
                (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
               jsonb_build_object('question', t2.question, 'children', array_to_json(ARRAY[]::varchar[]))
            FROM prm_tree_nodes t1
            LEFT JOIN prm_tree_nodes t2 ON t1.id = t2.parent_id
            WHERE t1.parent_id IS NULL
    
            UNION
    
            SELECT
               t1.id,
               t1.parent_id,
               t2.id,
               tree.path || '{children}' || (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
               jsonb_build_object('question', t2.question, 'children', array_to_json(ARRAY[]::varchar[]))
            FROM prm_tree_nodes t1
            LEFT JOIN prm_tree_nodes t2 ON t1.id = t2.parent_id
            INNER JOIN tree ON (t1.id = tree.child)
            WHERE t1.parent_id = tree.node_id
           )
           SELECT child as node_id, path, nested_json_tree FROM tree
           WHERE child IS NOT NULL ORDER BY PATH
        LOOP
           SELECT jsonb_insert(_json_output, _temprow.path, _temprow.nested_json_tree) INTO _json_output;
        END LOOP;
    
        RETURN query
           SELECT
            _json_output, prm_tree_nodes.tree_id
           FROM prm_tree_nodes
           WHERE parent_id IS NULL;
    END;
    $$ LANGUAGE plpgsql;

This function gives me a general json object that contains all of the trees, not filtered per the tree id, alongside the tree ids:

_json_output (jsonb)	tree_id (char var)
`{tree_a_root: {...tree_a, tree_b_root: {...tree_b}}`	tree_a
`{tree_a_root: {...tree_a, tree_b_root: {...tree_b}}`	tree_b

The wanted result is:

_json_output (jsonb)	tree_id (char var)
`{tree_a_root: {...}}` or smth similar	tree_a
`{tree_b_root: {...}}` or smth similar	tree_b

CodePudding user response：

Seems like you are trying to build a nested json via a set of primary keys.

Change 1:

First, we have to make two nested for loops - one for creating each tree based on its tree id and another for the recursive node json merging/building:

DECLARE
       _json_output jsonb;
       _temprow record;
       _tree_id text; --added due to another for loop

BEGIN
--for-loop iterating over all of your tree id's
FOR _tree_id IN SELECT tree FROM prm_decision_trees LOOP 

    ... <main query> ...

    RETURN QUERY SELECT _json_output, _tree_id;
END LOOP;
END;

Change 2:

Now, we need to filter all of the nodes of each specific tree in order to get the structure you want by adding

<Table>.tree_id = _tree_id

wherever you are filtering out nodes.

Change 3:

We need to change the returned columns names so it'll be unique:

_json_output ---> tree_output

tree_id ---> tree_id_output

TABLE(tree_output jsonb, tree_id_output character varying(11))

Summary

Putting all the changes together and we should get something like this that should work:

CREATE OR REPLACE FUNCTION nested_json_tree() 
RETURNS TABLE(tree_output jsonb, tree_id_output character varying(11)) AS $$
    DECLARE
       _json_output jsonb;
       _temprow record;
       _tree_id text; --added due to another for loop

    BEGIN
        FOR _tree_id IN SELECT tree FROM prm_decision_trees LOOP --for-loop iterating over all of your tree id's
            SELECT jsonb_build_object('question', question, 'children', array_to_json(ARRAY[]::varchar[]))
            INTO _json_output
            FROM prm_tree_nodes
            WHERE parent_id IS NULL AND tree_id = _tree_id; --filter each tree id for each row object
        
            FOR _temprow IN
                WITH RECURSIVE tree(node_id, ancestor, child, path, nested_json_tree) AS (
                    SELECT
                        t1.id,
                        NULL::VARCHAR,
                        t2.id,
                        '{children}'::text[] ||
                            (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
                        jsonb_build_object('question', t2.question, 'children', array_to_json(ARRAY[]::varchar[]))
                    FROM prm_tree_nodes t1
                    LEFT JOIN prm_tree_nodes t2 ON t1.id = t2.parent_id AND t2.tree_id = _tree_id --filter each tree id for each row object
                    WHERE t1.parent_id IS NULL AND t1.tree_id = _tree_id --filter each tree id for each row object
            
                    UNION
            
                    SELECT
                        t1.id,
                        t1.parent_id,
                        t2.id,
                        tree.path || '{children}' || (row_number() OVER (PARTITION BY t1.id ORDER BY t2.id) - 1)::text,
                        jsonb_build_object('question', t2.question, 'children', array_to_json(ARRAY[]::varchar[]))
                    FROM prm_tree_nodes t1
                    LEFT JOIN prm_tree_nodes t2 ON t1.id = t2.parent_id AND t2.tree_id = _tree_id --filter each tree id for each row object
                    INNER JOIN tree ON (t1.id = tree.child)
                    WHERE t1.parent_id = tree.node_id AND t1.tree_id = _tree_id --filter each tree id for each row object
                )                
                SELECT child as node_id, path, nested_json_tree 
                FROM tree
                WHERE child IS NOT NULL ORDER BY PATH

            LOOP
                SELECT jsonb_insert(_json_output, _temprow.path, _temprow.nested_json_tree) INTO _json_output;
            END LOOP;
        
            RETURN QUERY SELECT _json_output, _tree_id;
        END LOOP;
    END;
$$ LANGUAGE plpgsql;