How to Generate Nested JSON Objects from MySQL Relational Data Using JSON_OBJECT and JSON_ARRAY?

Generating Nested JSON Objects with MySQL Native JSON Functions

MySQL version 5.7.12 introduced native JSON functions, enabling the creation of JSON documents directly from relational data. To achieve this, one can leverage the JSON_OBJECT() and JSON_ARRAY() functions.

Consider the example tables provided:

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

The goal is to generate a JSON document with nested objects, resembling 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"
        }
    ]
}]

Resolving the Error

Previous attempts have encountered the error "Subquery returns more than 1 row" because the JSON object requires simple key-value pairs as inputs. Subqueries that return multiple rows need to be converted into a single string or JSON array.

Solution

To overcome this error, MySQL's GROUP_CONCAT() function can be employed. The following query accomplishes this:

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array(
                     (select GROUP_CONCAT(
                                 json_object('id',id,'parent_id',parent_id,'desc',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

To eliminate escape characters in the output, the query is modified to use CAST() and CONCAT() functions:

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 refined query produces the desired JSON output:

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

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