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;

Answer 2

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	{"dia": "110", "sys": "20"}
2	{"bpm": "200"}
3	{"bpm": "123", "dia": "", "sys": "1"}

CREATE PROCEDURE parse_json ()
BEGIN
DECLARE counter INT DEFAULT 0;
CREATE TEMPORARY TABLE tmp1 (id INT, all_keys JSON)
SELECT id, JSON_KEYS(value) all_keys
FROM test;
CREATE TEMPORARY TABLE tmp2 (id INT, one_key VARCHAR(255)) ENGINE = Memory;
REPEAT
    INSERT INTO tmp2
    SELECT id, JSON_EXTRACT(all_keys, CONCAT('$[',counter,']')) one_key
    FROM tmp1
    HAVING one_key IS NOT NULL;
    SET counter := counter + 1;
UNTIL NOT ROW_COUNT() END REPEAT;
SELECT id, 
       CAST(JSON_UNQUOTE(tmp2.one_key) AS CHAR) val1, 
       CAST(JSON_UNQUOTE(JSON_EXTRACT(test.value, CONCAT('$.', tmp2.one_key))) AS CHAR) val2
FROM test
JOIN tmp2 USING (id)
ORDER BY 1,2;
DROP TEMPORARY TABLE tmp1;
DROP TEMPORARY TABLE tmp2;
END

CALL parse_json

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

db<>fiddle here

If the datatype of the column with JSON values is VARCHAR/TEXT then the data should NOT contain duplicated keys in JSON values.