MySQL - 选择或提取 JSON 对象数据作为列和值

Question

我想在 MySQL 中将 JSON 对象数据(键/值)提取为多列/值。

假设我有以下数据:

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}}');

无论如何，上面的数据是否可以提取为以下输出。例如键名作为列名，值作为行。

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

请注意，我可以更改 jdoc 的 JSON 数据格式以获得所需的输出。

Answer 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

对于动态 key 列表使用存储过程:

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