mysql - 将 MySQL 中的 JSON 数组转换为行

Question

更新:现在可以通过 JSON_TABLE 函数在 MySQL 8 中实现:https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

我喜欢 MySQL 5.7 中的新 JSON 函数，但在尝试将 JSON 中的值合并到普通表结构中时遇到了问题。

获取 JSON、操作并从中提取数组等都很简单。一路都是 JSON_EXTRACT。但是反过来呢，从 JSON 数组到行呢？也许我对现有的 MySQL JSON 功能很感兴趣，但我一直无法弄清楚这一点。

例如，假设我有一个 JSON 数组，并且想要为数组中的每个元素及其值插入一行？我发现的唯一方法是编写一堆 JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') 等并将它们联合在一起。

或者，假设我有一个 JSON 数组并希望将其 GROUP_CONCAT() 为单个逗号分隔的字符串？

换句话说，我知道我可以做到这一点:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  FROM   
  (    
    SELECT 0 AS n    
    UNION    
    SELECT 1 AS n    
    UNION    
    SELECT 2 AS n    
    UNION    
    SELECT 3 AS n    
    UNION    
    SELECT 4 AS n    
    UNION    
    SELECT 5 AS n    
  ) x
WHERE x.n < JSON_LENGTH(@j);

但是这伤害了我的眼睛。还有我的心。

我怎样才能做这样的事情:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))

...并将数组中的值与 JSON 数组本身连接在一起？

我想我在这里寻找的是某种 JSON_SPLIT ，类似于:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val)
FROM
  JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

如果 MySQL 有一个正确的 STRING_SPLIT(val, 'separator') 表返回函数，我可以破解它(该死的逃脱)，但这也不可用。

Answer 1

确实，非规范化为 JSON 不是一个好主意，但有时您需要处理 JSON 数据，并且有一种方法可以将 JSON 数组提取到查询中的行中。

技巧是对临时或内联索引表执行联接，这为 JSON 数组中的每个非空值提供一行。即，如果您有一个值为 0、1 和 2 的表，您将其连接到包含两个条目的 JSON 数组“fish”，则 Fish[0] 与 0 匹配，从而生成一行，并且 Fish 1匹配 1，产生第二行，但 Fish[2] 为空，因此它与 2 不匹配，并且不会在连接中生成行。您需要索引表中的数字与 JSON 数据中任何数组的最大长度一样多。这有点像黑客，和OP的例子一样痛苦，但它非常方便。

示例(需要 MySQL 5.7.8 或更高版本):

CREATE TABLE t1 (rec_num INT, jdoc JSON);
INSERT INTO t1 VALUES 
  (1, '{"fish": ["red", "blue"]}'), 
  (2, '{"fish": ["one", "two", "three"]}');

SELECT
  rec_num,
  idx,
  JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

结果是:

+---------+-----+---------+
| rec_num | idx | fishes  |
+---------+-----+---------+
|       1 |   0 | "red"   |
|       1 |   1 | "blue"  |
|       2 |   0 | "one"   |
|       2 |   1 | "two"   |
|       2 |   2 | "three" |
+---------+-----+---------+

看起来 MySQL 团队可能会在 MySQL 8 中添加一个 JSON_TABLE 函数，以使这一切变得更容易。 ( http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/ ) (MySQL 团队已经添加了 JSON_TABLE 函数。)