Return json Hierarchical structure in single SQL query

Question

I need to create a SQL query (postgres 9.5.3) which returns a hierarchical json result. This is the code I wrote so far

WITH RECURSIVE q AS ( 
    WITH c AS (
        SELECT pc."Id", pc."Description"
        FROM "ProductCategories" pc 
        WHERE pc."Active" = true 
    )
    SELECT pc, ARRAY[c] as "Children", ARRAY[pc."Id"] as "Path" 
    FROM "ProductCategories" pc 
    LEFT JOIN c ON pc."Id" = c."Id"
    WHERE NULLIF(pc."ParentId", 0) IS NULL 
    AND pc."Active" = true 
    UNION ALL 
    SELECT pc_descendant, array_append(q."Children", c), q."Path" || pc_descendant."Id" 
    FROM q 
    JOIN "ProductCategories" pc_descendant ON pc_descendant."ParentId" = (q.pc)."Id" 
    LEFT JOIN c ON pc_descendant."Id" = c."Id"
    WHERE pc_descendant."Active" = true 
)
SELECT * FROM q

I have problem to create hierarchical object Children. For these structure

the array_append functions seems to append any children elements into single array:

A.Children = [ {B}, {C}, {D} ]    //for category A

I need structure:

  A.Children = [ {B, Children = [ {C, Children = [ {D} ] } ] } ]

How can I change my query to achieve this? Regards

Abelisto · Accepted Answer · 2018-05-01 23:19:36Z

8

Not sure that it is possible at least in the simple convenient way.

However it seems that it is simple using "true" recursion.

Here is simple example:

create temp table t(id int, parent int, name text) on commit drop;

insert into t values
  (1,null,'john'),
  (2,1,'jane'),
  (3,1,'jack'),
  (4,2,'julian');

create or replace function build_family(p_parent int) returns setof jsonb as $$

  select
    case 
      when count(x) > 0 then jsonb_build_object('name', t.name, 'family', jsonb_agg(f.x))
      else jsonb_build_object('name', t.name)
    end
  from t left join build_family(t.id) as f(x) on true
  where t.parent = p_parent or (p_parent is null and t.parent is null)
  group by t.id, t.name;

$$ language sql;


select jsonb_pretty(build_family) from build_family(null::int);

and result is

┌──────────────────────────────────────┐
│             jsonb_pretty             │
├──────────────────────────────────────┤
│ {                                   ↵│
│     "name": "john",                 ↵│
│     "family": [                     ↵│
│         {                           ↵│
│             "name": "jane",         ↵│
│             "family": [             ↵│
│                 {                   ↵│
│                     "name": "julian"↵│
│                 }                   ↵│
│             ]                       ↵│
│         },                          ↵│
│         {                           ↵│
│             "name": "jack"          ↵│
│         }                           ↵│
│     ]                               ↵│
│ }                                    │
└──────────────────────────────────────┘

I hope that you can adapt it for your data.

Good luck.

edited May 1, 2018 at 23:19

answered Jun 17, 2016 at 17:48

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

bobmarksie Over a year ago

This is brilliant - love this elegant solution which really shows off the power of PostgreSQL! I also updated the 'family', jsonb_agg(f.x) to 'family', case when jsonb_agg(f.x) != '[null]'::jsonb then jsonb_agg(f.x) else '[]'::jsonb end which returns ` ... "family": [] ... instead of ` ... "family": [null] ....

Abelisto Over a year ago

@bobmarksie It depends in requirements IMO. Additionally to your suggestion I'v updated the answer to remove unnecessary empty "family" key at all.

Jongz Puangput Over a year ago

Hi, can someone, in this case, one will happen in the first and second recursive loop.

Jongz Puangput Over a year ago

Is this possible, to rewirte in CTE ? (just for understanding on this query).

Michael Over a year ago

I think this should be doable with a recursive CTE. But I have struggled with the CTE solution and couldn't get rid of some duplicates. Your solution is very neat, and much easier to understand than the CTE. Thank you for sharing!

|

wgbeckmann · Accepted Answer · 2023-10-13 08:13:55Z

I just had the same Problem, but with whole rows. While solving it, I found an optimized version of the Idea:


create table fam(id int, parent int, name text) ;

insert into fam values
  (1,null,'john'),
  (2,1,'jane'),
  (3,1,'jack'),
  (4,2,'julian');

create or replace function build_family(p_parent int) returns setof jsonb as $$

  select jsonb_build_object('name', fam.name)
         ||jsonb_strip_nulls(
                  jsonb_build_object('family', (select jsonb_agg(build_family) from build_family(fam.id)))
            )
  from fam 
  where fam.parent = p_parent or (p_parent is null and fam.parent is null);

$$ language sql;


select jsonb_pretty(build_family) from build_family(null::int);

Result:

{
    "name": "john",
    "family": [
        {
            "name": "jane",
            "family": [
                {
                    "name": "julian"
                }
            ]
        },
        {
            "name": "jack"
        }
    ]
}

Collectives™ on Stack Overflow

Return json Hierarchical structure in single SQL query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related