how to extract json keys and values as table with 2 columns mysql 5.7-CodePudding

Having this in column named "value" on table named "test" with varchar data type:

'{"3": "3", "2": "7", "6": "1", "1": "2", "5": "5"}'

the output I need:

col1    col2
 3       3
 2       7
 6       1
 1       2
 5       5

I'm having difficulties to parse json as it's seems that only version 8 provides ability to work with json properly.

Maybe the are some thoughts how to solve it and extract key and values dynamically without hardcoding key as following in col1 and col2:

select 1 as 'col1', TRIM(BOTH '"' FROM (JSON_EXTRACT(value, '$."1"'))) 'col2'
from test
union all
select 2, TRIM(BOTH '"' FROM (JSON_EXTRACT(value, '$."2"')))
from test
union all
select 3, TRIM(BOTH '"' FROM (JSON_EXTRACT(value, '$."3"')))
from test
union all
select 5, TRIM(BOTH '"' FROM (JSON_EXTRACT(value, '$."5"')))
from test
union all
select 6, TRIM(BOTH '"' FROM (JSON_EXTRACT(value, '$."6"')))
from test

CodePudding user response：

It would be better to upgrade to MySQL 8 so you can use the JSON_TABLE() function.

It would also be better to avoid storing data in JSON, and instead store data as normal rows and columns, if you need to reference individual elements in SQL expressions. This is true whether you upgrade to MySQL 8 or not.

But here's a solution for your question:

You can get the keys of your JSON document as an array:

mysql> create table test ( value json);

mysql> insert into test set value = '{"3": "3", "2": "7", "6": "1", "1": "2", "5": "5"}';

mysql> select json_keys(value) as `keys` from test;
 --------------------------- 
| keys                      |
 --------------------------- 
| ["1", "2", "3", "5", "6"] |
 ---------------------------

The easiest way to pivot this array into rows is to have a table of integers.

mysql> create table numbers (n int primary key);

mysql> insert into numbers (n) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

mysql> select json_extract(json_keys(value), concat('$[', n, ']')) as `key` 
  from test cross join numbers where n <= 4;
 ------ 
| key  |
 ------ 
| "1"  |
| "2"  |
| "3"  |
| "5"  |
| "6"  |
 ------

Now you can use this result to do a self-join back to the document you started with, and extract the key.

mysql> select json_unquote(k.`key`) as col1,
  json_unquote(json_extract(test.value, concat('$.', k.`key`))) as col2
from (
  select json_extract(json_keys(value), concat('$[', n, ']')) as `key` from test cross join numbers where n <= 4
) as k
cross join test;

 ------ ------ 
| col1 | col2 |
 ------ ------ 
| 1    | 2    |
| 2    | 7    |
| 3    | 3    |
| 5    | 5    |
| 6    | 1    |
 ------ ------