如何将jsonMySQL数据转换为行和列

Question

我有这样的mysql表，其中包含id和json类型的列：

id	值
1	{"系统"："20"，"直径"："110"}
2	{"bpm"："200"}
3	{"bpm"："123"，"sys"："1"，"dia"："}

CREATE TABLE test (id INT, value JSON);
INSERT INTO test VALUES
(1,   '{"sys": "20", "dia": "110"}'),
(2,   '{"bpm": "200"}'),
(3,   '{"bpm": "123", "sys": "1", "dia": ""}');
SELECT id, CAST(value AS CHAR) value FROM test;

id	value
1	{"直径"："110"，"系统"："20"}
2	{quot；bpm"：quot；200"}
3	{quot；bpm"："123"，"dia"，"sys">

我能够找到下面的sql查询，它将返回它的数据：

select id as Id
,JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(value), CONCAT('$[', idx , ']'))) as val1
,JSON_UNQUOTE(JSON_EXTRACT(ic1.value, CONCAT('$.',JSON_EXTRACT(JSON_KEYS(value), CONCAT('$[', idx , ']'))))) as val2
from test as ic1
INNER JOIN (  
SELECT 0 as idx UNION ALL 
SELECT 1 as idx UNION ALL 
SELECT 2 as idx UNION ALL 
SELECT 3 as idx UNION ALL
SELECT 4 as idx UNION ALL 
SELECT 5 as idx UNION ALL
SELECT 6 as idx UNION ALL
SELECT 7 as idx UNION ALL
SELECT 8 
) AS Indices 
ON Indices.idx < JSON_LENGTH(JSON_KEYS(value))
ORDER BY id;