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

Question

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:

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');

I am trying to generate a JSON document that looks like this:

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

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.

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

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

I attempted to write the equivalent in MySQL as follows:

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

Both attempts fail with the following error:

Error Code: 1242. Subquery returns more than 1 row

Greg Faulk · Accepted Answer · 2016-05-27 20:06:28Z

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

hello, i am not able to use json_object this function. #1305 - FUNCTION json_object does not exist. What to do???
Check which version of Mysql you are using. The older versions do not support json_object
JSON aggregate functions JSON_ARRAYAGG() and JSON_OBJECTAGG() are now available in MySQL 8 mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions
This works fine but for two level only, can we get till 'n' level

JarmoP · Accepted Answer · 2019-11-15 21:28:22Z

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;

Ban Nguyen - VN · Accepted Answer · 2021-08-30 15:53:53Z

I tried group_concat solution but I found it's problems larger string because of group_concat limitations (group_concat_max_len). I wrote the new function resolve the problem about converting a string to JSON object as bellow and how to use it. Tested on MariaDB 10.5.12

Usage: https://i.sstatic.net/cWfd7.jpg

 CREATE FUNCTION `ut_tf_array`(input_json longtext) RETURNS longtext CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Function for transform json array agg' BEGIN DECLARE transformed_data_list longtext ; DECLARE record longtext ; DECLARE i_count int ; DECLARE i_count_items int ; SET i_count = 0; SET i_count_items = JSON_LENGTH(JSON_EXTRACT(input_json,'$')); SET transformed_data_list = '[]'; -- return array with length = zero IF input_json is NULL THEN RETURN transformed_data_list; END IF; WHILE i_count < i_count_items DO -- fetch into record SELECT JSON_EXTRACT( JSON_EXTRACT( input_json ,'$') , CONCAT('$[',i_count,']')) INTO record; -- append to transformed_data_list SELECT JSON_ARRAY_APPEND(transformed_data_list, '$', JSON_EXTRACT(record, '$')) into transformed_data_list; SET i_count := i_count + 1; END WHILE; -- done RETURN transformed_data_list; END

chirag chauhan · Accepted Answer · 2022-06-05 14:48:54Z

Below Query works for me.

SELECT JSON_ARRAYAGG(JSON_OBJECT('Id', p.id, 'desc', p.`desc`, 'child_objects', temp_json)) AS json_value FROM ( SELECT p.id, p.`desc`, JSON_ARRAYAGG(JSON_OBJECT('id', p.id, 'parent_id', p.parent_id, 'desc', p.`desc`)) AS temp_json FROM parent_table p GROUP BY p.id ) t;

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Linked

Related