JSON5.7-选择MySQL文档作为表

Question

我有一个下面的JSON数据格式，它有简单的键/值对数据。

CREATE TABLE test_table (id INT, jdoc JSON);

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

我需要选择以下格式的上述数据。即键名(键名是动态的)作为列名，值作为数据。

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

我使用JSON_TABLE从高级SO成员“阿金娜”那里得到了以下解决方案。但此功能仅在MySQL 8.0中支持

https://stackoverflow.com/a/69673530/1791406

在MySQL 5.7中有没有JSON_TABLE函数的替代品？或者获取任何线索来获得所需的结果。

Answer 1

是的，是老式的方法，使用json_extract和路径：

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=403c4a6fa81b514214d3f10ddb4cf50e

SELECT t.id, 
  t.jdoc->'$.CS' as CS, 
  t.jdoc->'$.Physics' as Physics, 
  t.jdoc->'$.Chemistry' as Chemistry
FROM test_table t

其中->是json_extract的速记运算符。根据您要获取的数据的格式，您可能需要使用->>，它是json_unquote(json_extract())的简写运算符。

您可以通过构建动态路径来支持未知键，但它的可读性会变得较差：

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=bb9bf699ace41e6dd0e475e60b60605b

SELECT t.id, 
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[0]'))) as k0,
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[1]'))) as k1,
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[2]'))) as k2
FROM test_table t;

您可以看到，除了列名之外，输出是相同的。如果列名很重要，则可以使用预准备语句动态生成带有字段名的查询，然后从中准备一条语句并执行它。请看这个小提琴：

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=fb2ab27407789bdfad04e0a53d9a0257

SELECT json_keys(t.jdoc) INTO @keys FROM test_table t LIMIT 1;
SET @query = CONCAT("SELECT t.id, "
"t.jdoc->'$.", json_extract(@keys, '$[0]'), "' as ", json_extract(@keys, '$[0]'), ", ",
"t.jdoc->'$.", json_extract(@keys, '$[1]'), "' as ", json_extract(@keys, '$[1]'), ", ",
"t.jdoc->'$.", json_extract(@keys, '$[2]'), "' as ", json_extract(@keys, '$[2]'),
"FROM test_table t;");

PREPARE stmt FROM @query;
EXECUTE stmt;

可读性已经不存在了，维护将是一件真正的苦差事。我相信变量已经被弃用了，所以不知道它会工作多久，通常建议避免运行动态生成的sql。但它将生成所需的结果。

请记住，执行动态生成的sql会给您带来许多可能的危害。您实际上是从其他地方获取文本，然后在您的系统上执行它。