MySQL Bugs: #94549: Iterating JSON Arrays and Objects

Bug #94549	Iterating JSON Arrays and Objects
Submitted:	4 Mar 2019 15:49	Modified:	21 Mar 2019 13:27
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am writing relatively complex applications using JSON in MySQL, trying to take advantage of the flexibility and functionality of both JSON and SQL.

I use the content of a JSON array or JSON object, would like to iterate to over them as if they were the rows of a table. However it is ugly and tricky to write.

It would be easier if there were built-in functions (at least syntactic sugar), and better still if it were optimized.

OR if user procedures or functions could return a Results Set that could be used as if it were a table in a SELECT, that would solve this problem and a ton of others.

This is how I do it:

mysql> select array,i,"do something with "||myArray.arrayElement
    -> from (
    ->      select array,number as i,json_unquote(json_extract(array,'$['||number||']')) as arrayElement
    ->      from (
    ->         select json_array("this", "that", "The Other") as array 
    ->      ) as sample_data
    ->             /* I use a table with one column 'number' a row for every number between 0 and a lot */
    ->       /* lets fake it out to make a standalone example */
    ->       join (select 0 as number union all select 1 union all select 2 union all select 3 union all select 5) as numbers
    ->       where number between 0 and json_length(array)-1
    ->  ) as myArray;
+-------------------------------+---+--------------------------------------------+
| array                         | i | "do something with "||myArray.arrayElement |
+-------------------------------+---+--------------------------------------------+
| ["this", "that", "The Other"] | 0 | do something with this                     |
| ["this", "that", "The Other"] | 1 | do something with that                     |
| ["this", "that", "The Other"] | 2 | do something with The Other                |
+-------------------------------+---+--------------------------------------------+
3 rows in set (0.02 sec)

mysql> select object,key_name,"do something with "  ,myObject.objectElement 
    -> from (
    ->      select  object
    ->      ,json_unquote(json_extract(json_keys(object),'$['||number||']')) as key_name
    ->      ,json_unquote(json_extract(object,'$.'||
    ->         json_unquote(json_extract(json_keys(object),'$['||number||']'))
    ->         )) as objectElement 
    ->         
    ->      from (
    ->         select json_object("this"," this value", "that", "that value","The_Other","boring!") as object 
    ->      ) as sample_data
    ->       /* I use a table with one column 'number' a row for every number between 0 and a lot */
    ->       /* lets fake it out to make a standalone example */
    ->       join (select 0 as number union all select 1 union all select 2 union all select 3 union all select 5) as numbers
    ->       where number between 0 and json_length(object)-1 
    ->                              
    ->  ) as myObject;
+-----------------------------------------------------------------------+-----------+--------------------+---------------+
| object                                                                | key_name  | do something with  | objectElement |
+-----------------------------------------------------------------------+-----------+--------------------+---------------+
| {"that": "that value", "this": " this value", "The_Other": "boring!"} | that      | do something with  | that value    |
| {"that": "that value", "this": " this value", "The_Other": "boring!"} | this      | do something with  |  this value   |
| {"that": "that value", "this": " this value", "The_Other": "boring!"} | The_Other | do something with  | boring!       |
+-----------------------------------------------------------------------+-----------+--------------------+---------------+
3 rows in set (0.01 sec)

BUT I'd like to be able to do it something like this

select object,i,"do something with "||myObject.objectElement
   from built_in_procedure( json_object("this", "that", "The Other") ) as myObject

 
 
select array,i,"do something with "||myArray.arrayElement
   from built_in_function( json_array("this", "that", "The Other") ) as myArray

OR this
   
    
select array,i,"do something with "||myArray.arrayElement
   from call user_procedure( json_array("this", "that", "The Other") ) as myArray
   
    
select object,i,"do something with "||myObject.objectElement
   from call user_procedure( json_object("this", "that", "The Other") ) as myObject

Yes I know it's not standard SQL! 

How to repeat:
select array,i,"do something with "||myArray.arrayElement
from (
     select array,number as i,json_unquote(json_extract(array,'$['||number||']')) as arrayElement
     from (
        select json_array("this", "that", "The Other") as array 
     ) as sample_data
            /* I use a table with one column 'number' a row for every number between 0 and a lot */
      /* lets fake it out to make a standalone example */
      join (select 0 as number union all select 1 union all select 2 union all select 3 union all select 5) as numbers
      where number between 0 and json_length(array)-1
 ) as myArray;

select object,key_name,"do something with "  ,myObject.objectElement 
from (
     select  object
     ,json_unquote(json_extract(json_keys(object),'$['||number||']')) as key_name
     ,json_unquote(json_extract(object,'$.'||
        json_unquote(json_extract(json_keys(object),'$['||number||']'))
        )) as objectElement 
        
     from (
        select json_object("this"," this value", "that", "that value","The_Other","boring!") as object 
     ) as sample_data
      /* I use a table with one column 'number' a row for every number between 0 and a lot */
      /* lets fake it out to make a standalone example */
      join (select 0 as number union all select 1 union all select 2 union all select 3 union all select 5) as numbers
      where number between 0 and json_length(object)-1 
                             
 ) as myObject

Suggested fix:
I'd like to be able to do it something like this

select object,i,"do something with "||myObject.objectElement
   from built_in_procedure( json_object("this", "that", "The Other") ) as myObject

 
 
select array,i,"do something with "||myArray.arrayElement
   from built_in_function( json_array("this", "that", "The Other") ) as myArray

OR this
   
    
select array,i,"do something with "||myArray.arrayElement
   from call user_procedure( json_array("this", "that", "The Other") ) as myArray
   
    
select object,i,"do something with "||myObject.objectElement
   from call user_procedure( json_object("this", "that", "The Other") ) as myObject

Yes I know it's not standard SQL!

Hi Dave,

JSON_TABLE was added in MySQL 8.0. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html. Would that fit the bill?

mysql> select * from json_table(json_array('this', 'that', 'other'), '$[*]' columns (i for ordinality, element varchar(100) path '$')) jt;
+---+---------+
| i | element |
+---+---------+
| 1 | this    |
| 2 | that    |
| 3 | other   |
+---+---------+

mysql> create table t(j json);
mysql> insert into t values (json_array('this', 'that', 'other'));
mysql> insert into t values (json_array('another', 'me'));
mysql> select j, i, concat('do something with ', element) from t, json_table(j, '$[*]' columns (i for ordinality, element varchar(100) path '$')) jt;
+---------------------------+---+---------------------------------------+
| j                         | i | concat('do something with ', element) |
+---------------------------+---+---------------------------------------+
| ["this", "that", "other"] | 1 | do something with this                |
| ["this", "that", "other"] | 2 | do something with that                |
| ["this", "that", "other"] | 3 | do something with other               |
| ["another", "me"]         | 1 | do something with another             |
| ["another", "me"]         | 2 | do something with me                  |
+---------------------------+---+---------------------------------------+

HI Dave,

I do hope that the answer that Knut provided for you would suffice. Please confirm, so that I can close this report.

Do note that new features that are introduced in 8.0 can not be ported back to earlier versions, since 8.0 has entirely new infra-structure.

Thanks in advance.

Yes the suggestion does the job. YOu may close the issue.

Thank you very much for the feedback.

Closed.