How to convert json MySQL data into rows and column

Question

I have mysql table like this which contain id and json type column:

id	value
1	{"sys": "20", "dia": "110"}
2	{"bpm": "200"}
3	{"bpm": "123", "sys": "1", "dia": ""}

Now, I want to have a MySQL query to which data should be as below in which id, val1 will contain keys of the json data and val2 will contain values of respective keys :

id	val1	val2
1	sys	20
1	dia	110
2	bpm	200
3	bpm	123
3	sys	1
3	dia

Note : I am using MySQL 5.7 version and the keys inside the JSON object are not fixed. It can be any number.

I want to know how I can achieve this using MySQL query

Thanks in Advance!!!

Answer 1

I am able to find the below sql query that will return me the data of it:

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;