MySQL – Select or Extract JSON object data as Column and value

I want to extract JSON object data (Key/value) as multiple column/value in MySQL.

Let say I have following data:

CREATE TABLE t3 (id INT, jdoc JSON);

INSERT INTO t3 VALUES 
  (1, '{"groups": {"CS":15, "Physics":20,"Chemistry":10}}'),
  (2, '{"groups": {"CS":6, "Physics":8,"Chemistry":5}}');

Is there anyway that above data can be extracted as following output. e.g. Key Name as column name and values as row.

id| CS | Physics | Chemistry
1 | 15 | 20      | 10
2 | 6  | 8       | 5

Please note, I can change the jdoc’s JSON data format in order to get required output.

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT test.id, 
       jsontable.cs, 
       jsontable.physics, 
       jsontable.chemistry
FROM test
CROSS JOIN JSON_TABLE(test.jdoc,
                      '$.groups' COLUMNS ( cs INT PATH '$.CS',
                                           physics INT PATH '$.Physics',
                                           chemistry INT PATH '$.Chemistry')) jsontable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f3d1bab18c3ea50706ddacd3cffd2dac

For dynamic key list use stored procedure:

CREATE PROCEDURE proc ()
BEGIN
SELECT CONCAT('SELECT test.id,jsontable.* FROM test CROSS JOIN JSON_TABLE(test.jdoc,"$.groups" COLUMNS(',
              GROUP_CONCAT(DISTINCT jsontable.`key`, ' INT PATH "$.', jsontable.`key`, '"' SEPARATOR ','),
              ')) jsontable')
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.jdoc, '$.groups'),
                      '$[*]' COLUMNS ( `key` VARCHAR(64) PATH '$' )) jsontable
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8011845c76cc60137d7fea5d3806761a

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating