How to Generate Nested JSON from Relational Tables in MySQL 5.7?

Generating Nested JSON with MySQL Native Functions

Problem:

Using MySQL 5.7.12's JSON functions, how can you construct a JSON document with embedded sub-objects from data in relational tables?

Database Setup:

Consider the following tables:

CREATE TABLE `parent_table` (
   `id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
   `id` int(11) NOT NULL,
   `parent_id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`,`parent_id`)
);
insert `parent_table` values (1,'parent row 1');
insert `child_table` values (1,1,'child row 1');
insert `child_table` values (2,1,'child row 2');

Desired JSON Output:

[{
    "id" : 1,
    "desc" : "parent row 1",
    "child_objects" : [{
            "id" : 1,
            "parent_id" : 1,
            "desc" : "child row 1"
        }, {
            "id" : 2,
            "parent_id" : 1,
            "desc" : "child row 2"
        }
    ]
}]

Solution:

To generate the desired nested JSON, you can use MySQL's GROUP_CONCAT function to concatenate multiple rows from the child_table into a string. Then, wrap the string in a JSON_ARRAY to convert it to a JSON array.

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',(select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    'id',id,'parent_id',parent_id,'desc',`desc`)),
                ']')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

This query will generate the JSON document exactly as specified in the desired output.

The above is the detailed content of How to Generate Nested JSON from Relational Tables in MySQL 5.7?. For more information, please follow other related articles on the PHP Chinese website!