How to Avoid \'Subquery Returns More Than 1 Row\' Errors When Generating Nested JSON Objects in MySQL?

Generating Nested JSON Objects with MySQL Native JSON Functions

Problem:

MySQL users seeking to utilize native JSON functions may face challenges in building nested JSON objects from a relational database. Specifically, attempting to create sub-objects from one-to-many relationships triggers "Subquery returns more than 1 row" errors.

Solution:

To effectively generate nested JSON objects using MySQL, it's crucial to avoid using subqueries that return multiple results in a parent object's input. Instead, concatenate the results using the GROUP_CONCAT function and convert them into a JSON array.

Example:

Building upon the example provided in the question, the following query will generate the desired JSON structure:

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 AS c
    WHERE c.parent_id = p.id
  )
)
FROM parent_table AS p;

This query utilizes CONCAT and GROUP_CONCAT to merge the desired sub-objects in the final result. Casting the concatenated string as a JSON data type ensures the proper formatting of the nested structure.

Result:

The rendered JSON object will closely align with the format specified in the question:

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

Additional Notes:

• The solution addresses the "Subquery returns more than 1 row" error by converting the subquery results into a comma-separated string.
• Using CONCAT and casting the result to JSON ensures that the nested structure is properly formatted.
• This approach is compatible with MySQL version 5.7.12 and above.

The above is the detailed content of How to Avoid \'Subquery Returns More Than 1 Row\' Errors When Generating Nested JSON Objects in MySQL?. For more information, please follow other related articles on the PHP Chinese website!