Create nested json from sql query postgres 9.4
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
Answer
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;