MySQL 5.7 -选择JSON文档作为表

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

我从高级SO成员Akina"那里得到了以下解决方案使用JSON_TABLE。但是这个函数只支持MySQL 8.0

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

在MySQL 5.7中是否有JSON_TABLE函数的替代品?或得到任何线索，以获得所需的结果。

Answer 1

是的，使用老式的方法，使用json_extract和paths:

https://dbfiddle.uk/?rdbms=mysql_5.7&小提琴= 403 c4a6fa81b514214d3f10ddb4cf50e

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&小提琴= 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&小提琴= 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会给您带来许多可能的危害。你实际上是从其他地方获取文本，然后在你的系统上执行它。