Return json Hierarchical structure in single SQL query

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.