Build json object include array object in PostgreSQL single query

Question

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

Answer 1

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

Demo here