MySQL - Select or Extract JSON object data as Column and value-CodePudding

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.

CodePudding user response：

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