Returning Postgres Nested JSON Array

The table skill should look like 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 be serial!
);

Use jsonb_build_object() and jsonb_agg(). Note, the query is somehow similar to the expected object.

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

I've wrapped the result with jsonb_pretty() to get a 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 that the order of elements of a json object is undefined.