I have table level_one_table
level_two_table
level_three_table
level_one_table
to level_two_table
one to multiple rows
level_two_table
to level_three_table
one to one row
Goal: select them all return data like below:
{
"level_one_table": {
"id": ..,
"... all other field in level_one_table"
"level_two_table": [{
"id": ..,
"... all other field in level_two_table",
"level_three_table": {
"id": ..,
"... all other field in level_three_table"
}
}]
}
}
level_one_table
:
"id" | ... |
---|---|
1 | ... |
2 | ... |
level_two_table
:
"id" | "fk_level_one_id" | ... |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
level_two_table
:
"id" | "fk_level_two_id" | ... |
---|---|---|
1 | 1 | ... |
2 | 2 | ... |
I used loop to select level two and level three table, is there a way to make it in single query?
I tried json_build_object
but I dont know how to select level_two_table
multiple rows make it as array object? and also select more deep level_three_table
an object?
SELECT json_build_object(
'id', t0.id,
'level_two_table': t2.*
?? make level_three_table inside level_two_table as an object
) FROM level_one_table t0
LEFT JOIN level_two_table t1 ON t0.id = t1.fk_level_one_id
LEFT JOIN level_three_table t2 ON t1.id = t2.fk_level_two_id
Nested calls to jon_build_object
are supported in postgres.
You need to build the expression step by step starting from the most nested objects. Note that the expression is somehow similar to the result.
jsonb_pretty()
used here to get a nice output
select jsonb_pretty(
json_build_object(
'level_one_table',
json_build_object(
'id', t0.id,
'level_two_table',
json_arrayagg(
json_build_object(
'id', t1.id,
'colmun2', t1.colmun2,
'level_three_table',
json_build_object(
'id', t2.id,
'colmun3', t2.colmun3
)
)
)
)
)::jsonb
) as json_obj
from level_one_table t0
left join level_two_table t1 ON t0.id = t1.fk_level_one_id
left join level_three_table t2 ON t1.id = t2.fk_level_two_id
group by t0.id