Creating Nested JSON Response from One-to-Many Tables in MySQL

database-designjsonMySQLoptimizationperformancequery-performance

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

Best Answer

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

innoDB

is bad for dml statement, #MyISAM is better. Innodb is try to commit all INSERTing data to disk and returns success after it pined.

when you need a big insert data you can avoid auto-commit=0 to get faster. or you can check this for hint.

MySQL – How to Execute Complex Nested Select SQL

I have tested this query:

SELECT budget.aid,
       sum(CASE change_type.table2_id WHEN 1 THEN 1 ELSE 0 END) as login,
       sum(CASE change_type.table2_id WHEN 3 THEN 1 ELSE 0 END) as search,
       CASE budget.table2_id WHEN 2 THEN IF(LEFT(budget.message, 8) = 'increase', 1, -1) ELSE 0 END as budget
FROM table1 budget  
LEFT JOIN table1 change_type 
ON budget.aid = change_type.aid 
AND change_type.date BETWEEN budget.date - INTERVAL 1 DAY AND budget.date
AND change_type.table2_id <> 2
WHERE budget.table2_id = 2
GROUP BY budget.aid, budget.date;

It was a fun query, I made it with joins, maybe the subselect version could be easier to understand. I perform SELF join to match budgets with other actions in the range requested. Please note that it filters other budget actions and that you will get an overlap if at least 2 budget actions are performed within 1 day.

To fill the second and third column, a sum of a CASE is performed after the grouping by aid and the reference date.

This is the result:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     0 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

Please note that no login for the aid 123 so this query returns the right results. There is a mistake on your example. Alternatively, if you do not want to have into account the aid for the actions, then the result would be:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     1 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

To obtain this, remove the budget.aid = change_type.aid AND from my query.

Also please note that no query has been done on table2, so I assume values are hardcoded and do not change. You can expand the query to join the other table if that is not correct.

Best Answer

Related Solutions

Mysql – poor and/or unreliable InnoDB performance

innoDB

MySQL – How to Execute Complex Nested Select SQL

Related Question