Create nested json from sql query postgres 9.4

You should build a hierarchical query to get a hierarchical structure as a result.

You want to have many persons in a single json object, so use json_agg() to gather persons in a json array.Analogically, a person can have multiple cars and you should place cars belonging to a single person in a json array. The same applies to cars and wheels.

select    json_build_object(        'persons', json_agg(            json_build_object(                'person_name', p.name,                'cars', cars            )        )    ) personsfrom person pleft join (    select         personid,        json_agg(            json_build_object(                'carid', c.id,                    'type', c.type,                'comment', 'nice car', -- this is constant                'wheels', wheels                )            ) cars    from        car c        left join (            select                 carid,                 json_agg(                    json_build_object(                        'which', w.whichone,                        'serial number', w.serialnumber                    )                ) wheels            from wheel w            group by 1        ) w on c.id = w.carid    group by personid) c on p.id = c.personid;

The (formatted) result:

{    "persons": [        {            "person_name": "Johny",            "cars": [                {                    "carid": 1,                    "type": "Toyota",                    "comment": "nice car",                    "wheels": [                        {                            "which": "front",                            "serial number": 11                        },                        {                            "which": "back",                            "serial number": 12                        }                    ]                },                {                    "carid": 2,                    "type": "Fiat",                    "comment": "nice car",                    "wheels": [                        {                            "which": "front",                            "serial number": 21                        },                        {                            "which": "back",                            "serial number": 22                        }                    ]                }            ]        },        {            "person_name": "Freddy",            "cars": [                {                    "carid": 3,                    "type": "Opel",                    "comment": "nice car",                    "wheels": [                        {                            "which": "front",                            "serial number": 3                        }                    ]                }            ]        }    ]}

If you are not familiar with nested derived tables you may use common table expressions. This variant illustrates that the query should be built starting from the most nested object toward the highest level:

with wheels as (    select         carid,         json_agg(            json_build_object(                'which', w.whichone,                'serial number', w.serialnumber            )        ) wheels    from wheel w    group by 1),cars as (    select         personid,        json_agg(            json_build_object(                'carid', c.id,                    'type', c.type,                'comment', 'nice car', -- this is constant                'wheels', wheels                )            ) cars    from car c    left join wheels w on c.id = w.carid    group by c.personid)select    json_build_object(        'persons', json_agg(            json_build_object(                'person_name', p.name,                'cars', cars            )        )    ) personsfrom person pleft join cars c on p.id = c.personid;

I've come up with this solution. It's quite compact and works in any given case.Not sure however what the impact is on performance when comparing to other solutions which make more use of json_build_object. The advantage of using row_to_json over json_build_object is that all the work is done under the hood, which makes the query more readable.

SELECT json_build_object('persons', json_agg(p)) personsFROM (       SELECT         person.name person_name,         (           SELECT json_agg(row_to_json(c))           FROM (                  SELECT                    id carid,                    type,                    (                      SELECT json_agg(row_to_json(w))                      FROM (                             SELECT                               whichone which,                               serialnumber                             FROM wheel                             WHERE wheel.carid = car.id                           ) w                    )  wheels                  FROM car                  WHERE car.personid = person.id                ) c         ) AS        cars       FROM person) p