How to Generate Nested JSON Objects from One-to-Many Relationships in MySQL?

Generating Nested JSON Objects with MySQL Native JSON Functions

This article demonstrates how to utilize MySQL version 5.7.12's native JSON functions to construct nested JSON documents representing one-to-many relationships in relational data.

Problem Statement

Given two tables, parent_table and child_table, representing a parent-child relationship, the goal is to generate a JSON document with the following structure:

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

Initially, attempts to create the nested JSON objects using simple nested subqueries resulted in an error because MySQL expects simple object pairs in the parent JSON object. To resolve this, we utilize the GROUP_CONCAT function to concatenate multiple subquery results into a single string, and then convert that result into a JSON array.

However, the resulting JSON output contains escape characters due to the subquery's treatment as a string. To remedy this, we use the CAST and CONCAT functions to create the JSON array in the subquery directly, rather than relying on concatenation in the main 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 produces the desired 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 One-to-Many Relationships in MySQL?. For more information, please follow other related articles on the PHP Chinese website!