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

by

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
    }]  
}'

More Related Contents:

  1. How to search JSON data in MySQL?
  2. How to convert result table to JSON array in MySQL
  3. Storing Data in MySQL as JSON
  4. MySQL JSON: How to find object from key value
  5. Using MySQL JSON field to join on a table
  6. How can write queries in MySQL that can parse JSON data in a column?
  7. Convert JSON array in MySQL to rows
  8. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  9. How to retrieve JSON data from MySQL?
  10. How to update JSON data type column in MySQL 5.7.10?
  11. How to create and insert a JSON object using MySQL queries?
  12. MySQL search json value by key in array
  13. MySQL vs. JSON – Why? [closed]
  14. How to create this type of JSON data from PHP [closed]
  15. How do I escape special characters in MySQL?
  16. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  17. SQL counting all rows instead of counting individual rows
  18. MySQL – ERROR 1045 – Access denied
  19. Remote Connections Mysql Ubuntu
  20. MySQL InnoDB not releasing disk space after deleting data rows from table
  21. In MySQL, should I quote numbers or not?
  22. Dump all tables in CSV format using ‘mysqldump’
  23. MySQL Order before Group by
  24. “193: %1 is not a valid Win32 application” bug with a new Rails Application
  25. How to display open transactions in MySQL
  26. MySQL query, MAX() + GROUP BY
  27. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  28. Using SUM on FLOAT data
  29. Duplicate entry for key ‘group_key’
  30. SQL query with avg and group by

Leave a Comment