Original Stackoverflow thread:
https://stackoverflow.com/questions/42222968/create-nested-json-from-sql-query-postgres-9-4/42226253#42226253
Suppose we have this tables:
person
car
wheel
And the relation between is:
person:car = 1:N
car:wheel = 1:N
We need to build some nested JSON Object with SQL Query to get the summary about details of each car this person has, what would you do ?
The Goal
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
{
"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
}
]
}
]
}
]
}
|
Approach 1 - Left Join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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;
|
Approach 2 - Put sub-query in SELECT-List with json_build_object and json_agg
This is the SQL query based on Nico Van Belle’s answer, but I replaced row_to_json with json_buid_object.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
select json_build_object(
'persons', (
SELECT json_agg(
json_build_object(
'person_id',id,
'cars', (
SELECT json_agg(
json_build_object(
'car_id', car.id,
'wheels', (
SELECT json_agg(
json_build_object(
'wheel_id', wheel.id,
'whichone', wheel.whichone,
'serialnumber', wheel.serialnumber,
'car_id', wheel.carid
)
)
FROM wheel WHERE wheel.carid = car.id
)
)
) FROM car WHERE id = person.id
)
)
) FROM person
)
);
|
You can view the result ojnline with db<>fiddle
Why Cost is so high ?
- Each Sub-node has to be executed
N times, where N is number of person
Query Plan
Summary
I think putting sub-query in SELECT-List is elegant, but it’s costly.
https://medium.com/@e850506/note-more-nested-json-5f3c1e4a87e