MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;

Records: 3  Duplicates: 0  Warnings: 0




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

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