How do I generate nested json objects using mysql native json functions?

For MariaDb, CAST AS JSON does not work. But JSON_EXTRACT may be used to convert a string to a JSON object:

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',JSON_EXTRACT(IFNULL((select
    CONCAT('[',GROUP_CONCAT(
      json_object('id',id,'parent_id',parent_id,'desc',`desc`)
    ),']')   
   from child_table where parent_id = p.id),'[]'),'$')
 ) from parent_table p;

The reason you are getting these errors is that the parent json object is not expecting a result set as one of its inputs, you need to have simple object pairs like {name, string} etc bug report - may be available in future functionality... this just means that you need to convert your multi row results into a concatination of results separated by commas and then converted into a json array.

You almost had it with your second example.

You can achieve what you are after with the GROUP_CONCAT function

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;

This almost works, it ends up treating the subquery as a string which leaves the escape characters in there.

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

In order to get this working in an appropriate format, you need to change the way you create the JSON output as follows:

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 will give you the exact result you require:

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