How to Generate Nested JSON Objects from Relational Tables in MySQL Using Native JSON Functions?

Generating Nested JSON Objects in MySQL with Native JSON Functions

MySQL native JSON functions provide a powerful mechanism for working with JSON data within the database. When dealing with relational tables containing one-to-many relationships, generating nested JSON objects can be challenging. In this article, we will explore how to achieve nested JSON objects using MySQL's native JSON functions.

The Problem

Consider the following scenario:

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');

The goal is to generate a JSON document that resembles the following:

[
    {
        "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"
            }
        ]
    }
]

The Solution

The key to generating nested JSON objects lies in the GROUP_CONCAT function. By combining the GROUP_CONCAT function with the JSON_OBJECT function, we can concatenate multiple JSON objects into a single array.

Consider the following query:

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 generates the desired nested JSON object by first using GROUP_CONCAT to concatenate the JSON objects for child rows into a JSON array. Then, the CONCAT function is used to wrap the JSON array in square brackets and create a valid JSON string. Finally, the CAST function converts the string into a JSON object.

Conclusion

By leveraging the GROUP_CONCAT and JSON_OBJECT functions, we can generate complex nested JSON objects from relational tables in MySQL using native JSON functions. This capability unlocks the potential for efficient data manipulation and exchange using JSON in MySQL.

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