title: How do I generate nested json objects using mysql native json functions?
body: <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>