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

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

Linda Hamilton

Release： 2024-11-29 06:41:10

Original

578 people have browsed it

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

Copy after login

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

Copy after login

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;

Copy after login

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;

Copy after login

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
    }]  
}'

Copy after login

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!