Create nested json from sql query postgres 9.4

Question

I need to get as a result from query fully structured JSON. I can see in postgres that there are some built in functions that may be useful.

As an example I created a structure as follows:

    -- Table: person

-- DROP TABLE person;

CREATE TABLE person
(
  id integer NOT NULL,
  name character varying(30),
  CONSTRAINT person_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE person
  OWNER TO postgres;

  -- Table: car

-- DROP TABLE car;

CREATE TABLE car
(
  id integer NOT NULL,
  type character varying(30),
  personid integer,
  CONSTRAINT car_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE car
  OWNER TO postgres;

  -- Table: wheel

-- DROP TABLE wheel;

CREATE TABLE wheel
(
  id integer NOT NULL,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  CONSTRAINT "Wheel_PK" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE wheel
  OWNER TO postgres;

And some data:

INSERT INTO person(id, name)
VALUES (1, 'Johny'),
       (2, 'Freddy');

INSERT INTO car(id, type, personid)
VALUES (1, 'Toyota', 1),
       (2, 'Fiat', 1),    
       (3, 'Opel', 2);     

INSERT INTO wheel(id, whichone, serialnumber, carid)
VALUES (1, 'front', '11', 1),
       (2, 'back', '12', 1),
       (3, 'front', '21', 2),
       (4, 'back', '22', 2),
       (5, 'front', '3', 3);

As a result I would like to have one JSON object which would contain list of person, each person will have list of cars and each car list of wheels.

I tried something like that but it isnt something that I want:

select json_build_object(
    'Persons', json_build_object(
    'person_name', person.name,
    'cars', json_build_object(
        'carid', car.id,    
        'type', car.type,
        'comment', 'nice car', -- this is constant
        'wheels', json_build_object(
            'which', wheel.whichone,
            'serial number', wheel.serialnumber
        )

    ))
)

from
person 
left join car on car.personid = person.id
left join wheel on wheel.carid = car.id

I suppose that I'm missing some group by and json_agg but I'm not sure how to do this.

I would like to have as a result something like this:

{ "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": 33
            }]
          }]
    }]
}

http://www.jsoneditoronline.org/?id=7792a0a2bf11be724c29bb86c4b14577

Solution 1

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
            )
        )
    ) persons
from person p
left 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
            )
        )
    ) persons
from person p
left join cars c on p.id = c.personid;

Solution 2

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)) persons
FROM (
       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

Comments

[Snorlax]

Ok and lets say that in addition car may have 0 or 1 engine. How to nest it as object not array of objects?

[Nico Van Belle]

then just add engine next to wheels in a subselect and transform it to json with the to_json function. SELECT json_build_object('persons', json_agg(p)) persons FROM ( SELECT person.name person_name, ( SELECT json_agg(row_to_json(c)) FROM ( SELECT id carid, type, (SELECT to_json(e) FROM ( SELECT horsepower FROM engine where engine.carid = car.id ) e ) engine, ( SELECT json_agg(row_to_json(w)) FROM ( SELECT whichone, serialnumber FROM wheel WHERE wheel.carid = car.id ) w ) wheels FROM car WHERE car.personid = person.id ) c ) AS cars FROM person ) p

[Snorlax]

And what to do if I would like to limit results only to first 10 persons from db?

[klin]

You should replace person with a derived table, i.e. instead of from person p use from ( select * from person... limit 10 ) p.

[Snorlax]

Perfect! And one more thing, wha if Im sure that all person have only one car and I dont want to display array of objects but object in the results json? How to easily modify it?

[klin]

Easly: just remove json_agg() and group by in the second query, like here. Formally it would be better to build a query with two (instead three) levels.

[Snorlax]

Super thanks, and one more small issue, I would like to make it this way that if cars list is empty the results shoul not be "cars": null but simply it should not appear in the result

[Michael]

How does the query know where to look for personid? Its declaration is not explicit

[mcpolandc]

@klin Thanks for this! Your solution fixed my issue and you levelled up my SQL game. I now have an understanding of how to use JSON functions and it almost looks like JSON :mind_blown:

[Josh Mc]

For those trying to write complex multi-level aggregates I would advise writing it out as a CTE (last example) as shown above as this is much simpler to comprehend then from there it shouldn't be too hard to move it back to subqueries (1st example) by just copying each expression into the matching table below until you have a single expression.