How can I perform a JSON recursive nested aggregation using SQL

Question

I have some data:

rank_ | data | parent_rank
--------------------------
1     | a    | NULL
2     | b    | 1
3     | c    | 2
4     | d    | 2
5     | e    | NULL
6     | f    | NULL
7     | g    | 6
8     | h    | 6

that I'd like to transform to nested form:

rank_ | nested
--------------------------------------------------------------------------------------
1     | {"D": "a", "C": [{"C": [{"C": [], "D": "c"}, {"C": [], "D": "d"}], "D": "b"}]}
5     | {"D": "e", "C": []}
6     | {"D": "f", "C": [{"C": [], "D": "g"}, {"C": [], "D": "h"}]}

where "C" is children and "D" is data.

So far I have the following code, but I've been so far unable to make it nest to arbitrary depth: https://www.db-fiddle.com/f/g7RFZQRX5JrzaYPXs62BNe/0

dani herrera · Accepted Answer · 2019-02-12 17:29:48Z

Le'ts supose we have the table o with data:

create table o (rank_ int, data varchar(10), parent_rank int) ;
insert into o (VALUES 
        (1, 'a', NULL),
        (2, 'b', 1),
        (3, 'c', 2),
        (4, 'd', 2),
        (5, 'e', NULL),
        (6, 'f', NULL),
        (7, 'g', 6),
        (8, 'h', 6)
    );

Then, you can write a simple recursive function like:

CREATE FUNCTION C(r INT) RETURNS JSONB AS
  ' BEGIN
      return (             
            jsonb_build_object(
                ''D'', ( select 
                         o.data 
                         from o
                         where o.rank_=r), 
                ''C'', ( select 
                         coalesce(
                            array_to_json(array_agg( C(o.rank_)  
                                          ORDER BY o.rank_))::JSONB,
                            ''[]''::JSONB )
                         from o
                         where o.parent_rank=r)
               )
            );

    END
    '
  LANGUAGE plpgsql;

And call the function for root notes:

SELECT o.rank_, c(o.rank_)
from o
where o.parent_rank is null

View on DB Fiddle:

1   {"C":[{"C":[{"C":[],"D":"c"},{"C":[],"D":"d"}],"D":"b"}],"D":"a"}
5   {"C":[],"D":"e"}
6   {"C":[{"C":[],"D":"g"},{"C":[],"D":"h"}],"D":"f"}

Collectives™ on Stack Overflow

How can I perform a JSON recursive nested aggregation using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related