postgresql - Returning Postgres Nested JSON Array -

i have been searching around answer haven't found close enough looking for.

i have 2 tables:

create table skill_tree (     id serial primary key,     name text not null,     description text not null ); 

and

create table skill (     id serial primary key,     name text not null,     description text not null,     duration integer not null,     cooldown integer not null,     skill_tree_id serial references skill_tree(id) ); 

i trying return json postgres db in such way structured so:

[{     "id": 1,     "name": "skill tree 1",     "description": "skill tree description",     "skills": [{         "id": 1,         "name": "skill 1",         "description": "skill 1 desc",         "duration": 10,         "cooldown": 20     }, {         "id": 2,         "name": "skill 2",         "description": "skill 2 desc",         "duration": 20,         "cooldown": 30     }] }] 

i able similar here [postgresql , nodejs/pg, return nested json wasn't able retrieve more skill's name.

the table skill should this:

create table skill (     id serial primary key,     name text not null,     description text not null,     duration integer not null,     cooldown integer not null,     skill_tree_id integer references skill_tree(id) -- cannot serial! ); 

use jsonb_build_object() , jsonb_agg(). note, query somehow similar expected object.

select jsonb_pretty(jsonb_agg(js_object)) result (     select          jsonb_build_object(             'id', id,              'name', name,              'description', description,              'skills', jsonb_agg(skill)         ) js_object     (         select              t.*,              jsonb_build_object(                 'id', s.id,                  'name', s.name,                  'description', s.description,                  'duration', duration,                  'cooldown', cooldown             ) skill         skill_tree t         join skill s on s.skill_tree_id = t.id         ) s     group id, name, description     ) s; 

i've wrapped result jsonb_pretty() nice output:

                     result                       -------------------------------------------------  [                                              +      {                                          +          "id": 1,                               +          "name": "skill tree 1",                +          "skills": [                            +              {                                  +                  "id": 1,                       +                  "name": "skill 1",             +                  "cooldown": 20,                +                  "duration": 10,                +                  "description": "skill 1 desc"  +              },                                 +              {                                  +                  "id": 2,                       +                  "name": "skill 2",             +                  "cooldown": 30,                +                  "duration": 30,                +                  "description": "skill 2 desc"  +              }                                  +          ],                                     +          "description": "skill tree description"+      }                                          +  ] 

note order of elements of json object undefined.