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!!!

Seems like it would have been a better idea to design the database as a relational database should be designed. Is it too late to refactor the database design into something that better fits the relational model — RiggsFolly, Feb 18 '22 at 11:00
JSON (JavaScript Object Notation) is typically used by a programming language, not a query language. So, yes, you should use MySQL queries to retrieve and store the data, but to disentangle the JSON I would use something like PHP/Python/C/etc. MySQL does have [some support for JSON](https://dev.mysql.com/doc/refman/8.0/en/json.html), perhaps that would suffice, but I doubt it. — KIKO Software, Feb 18 '22 at 11:08
*I have mysql table like this which contain id and json type column:* Shown values can be JSON values stored in string-datatype column, not in JSON-datatype column. *val1 will contain keys of the json data and val2 will contain values of respective keys ... I want to know how I can achieve this using MySQL query* On your version this can be performed only in iterative stored procedure. To perform this in single query you must upgrade your server till 8.0.4 at least. — Akina, Feb 18 '22 at 11:20
Arghh. This is an example of a nightmarish database design made possible when it's possible to stash arbitrary serialized objects (JSON objects) in database columns. PLEASE add a vitals table with `id`, `vital_name`, and `value` columns. Notice you can also add timestamps or other attributes to the vitals. PLEASE don't put JSON in your table columns, at least not for data you ever want MySQL to manipulate. — O. Jones, Feb 18 '22 at 11:33

Akina · Answer 1 · 2022-02-18T12:01:43.807

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.

score 0 · Accepted Answer · answered Feb 23 '22 at 09:26

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;

How to convert json MySQL data into rows and column

2 Answers2

Linked