时间戳

首页 > 解决方案 > MySQL 5.7 - 选择 JSON 文档作为表

json - MySQL 5.7 - 选择 JSON 文档作为表

问题描述

我有以下 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

JSON_TABLEMySQL 5.7 中是否有任何功能替代方案。或获得任何线索以获得所需的结果。

标签： jsonmysql-5.7

解决方案

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

推荐阅读