How do I generate nested json objects using mysql native json functions? : <p>Using only the native JSON fuctions (no PHP, etc) in MySQL version 5.7.12 (section 13.16 in the manual) I am trying to write a query to generate a JSON document from relational tables that contains a sub object. Given the following example:</p>

<pre><code>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');
</code></pre>

<p>I am trying to generate a JSON document that looks like this:  </p>

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

<p>I am new to MySQL and suspect there is a SQL pattern for generating nested JSON objects from one to many relationships but I'm having trouble finding it.</p>

<p>In Microsoft SQL (which I'm more familiar with) the following works:  </p>

<pre><code>select 
 [p].[id]
,[p].[desc]
,(select * from [dbo].[child_table] where [parent_id] = [p].[id] for json auto) AS [child_objects]
from [dbo].[parent_table] [p]
for json path
</code></pre>

<p>I attempted to write the equivalent in MySQL as follows:  </p>

<pre><code>select json_object(
 'id',p.id 
,'desc',p.`desc`
,'child_objects',(select json_object('id',id,'parent_id',parent_id,'desc',`desc`) 
                  from child_table where parent_id = p.id)
)
from parent_table p;

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array((select json_object('id',id,'parent_id',parent_id,'desc',`desc`) 
                              from child_table where parent_id = p.id))
 )
 from parent_table p
</code></pre>

<p>Both attempts fail with the following error:</p>

<pre><code>Error Code: 1242. Subquery returns more than 1 row
</code></pre>
