Nested json response from 3 one to many tables

Question

I am designing data model in following way:

Table 1:
     id: number
    somedata: String
    somedata: String

Table 2:
    id: number
    Table1_id: number
    somedata: String
    somedata: String

Table 3:
    id: number
    Table2_id: number
    somedata: String

1st table is joined to 2nd table with one to many relationship. 2nd table is joined to 3rd table with one to many relationship. I am using java, JPA. I have to return JSON via rest api in following format:

{
  "table1_id": 1,
  "somedata": "somedata",
  "table2_data": [
    {
      "table2_id": 1,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 1,
          "somedata": "somedata"
        },
        {
          "table3_id": 2,
          "somedata": "somedata"
        }
      ]
    },
    {
      "table2_id": 2,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 3,
          "somedata": "somedata"
        },
        {
          "table3_id": 4,
          "somedata": "somedata"
        }
      ]
    }
  ]
}

If I use mysql and above table structure I will need to use minimum 3 database calls to fetch from 3 tables separately and will have to construct my json. Is there a way where I can reduce database calls and also reduce effort to construct my json? I am open to change database(mysql) also. I just need fast, optimised and best solution. Note that table1, table2 and table3 has different fields so I can not denormalize them to single table.

I already have seen this question and my question is different than this: https://stackoverflow.com/questions/11047151/nested-json-from-3-one-to-many-tables

Answer 1

In PostgreSQL:

SELECT
    row_to_json (r1.*)
FROM
    (
        SELECT 
            table_1_id, 
            somedata,
            array(
                SELECT
                    row_to_json (r2.*)
                FROM
                (
                SELECT 
                        table_2_id, 
                        somedata,
                        array(
                            SELECT
                                row_to_json(r3.*, true)
                            FROM
                            (
                                SELECT
                                    table_3.table_3_id,
                                    table_3.somedata
                                FROM
                                    table_3
                                WHERE
                                    table_3.table_2_id = table_2.table_2_id
                                ORDER BY
                                    table_3_id
                            ) r3

                        ) AS table_2_data
                  FROM 
                        table_2  
                 WHERE 
                        table_2.table_1_id = table_1.table_1_id 
                 ORDER BY 
                        table_2_id
                ) r2
           ) AS table_2_data
        FROM 
            table_1
    ) AS r1

(with a few caveats, because your first level response is not an array; and I think it should).

The syntax is a bit convoluted and can probably be simplified

Test it at http://rextester.com/SEZ32413