Returning Postgres Nested JSON Array

Question

I have been searching around for an answer for this but haven't found anything close enough to what I was looking for.

I have two 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 am trying to return JSON from my Postgres DB in such a way that it is structured like 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 was able to get something similar from here PostgreSQL and nodejs/pg, return nested JSON but wasn't able to retrieve anything more than the skill's name.

Answer 1

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.