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!