在名为"值";在名为"的表上;测试";varchar数据类型:
‘{"3":"3","2":"7","6":"1","1":"2","5":"5"}’
我需要的输出:
col1 col2
3 3
2 7
6 1
1 2
5 5
我在解析json时遇到了困难,因为似乎只有版本8提供了正确处理json的能力。
也许有一些想法可以解决这个问题,并在没有硬编码密钥的情况下动态提取密钥和值,如col1和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
最好升级到MySQL 8,这样您就可以使用JSON_TABLE((函数。
如果需要引用SQL表达式中的单个元素,最好避免将数据存储在JSON中,而是将数据存储为普通的行和列。无论您是否升级到MySQL 8,这都是正确的。
但这里有一个解决你的问题的方法:
您可以将JSON文档的密钥作为数组:
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"] |
+---------------------------+
将此数组转换成行的最简单方法是使用一个整数表。
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" |
+------+
现在,您可以使用此结果对开始使用的文档进行自联接,并提取密钥。
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 |
+------+------+