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] 为 null，因此它不匹配 2，并且不会在连接中产生一行。您需要索引表中的数字与 JSON 数据中任何数组的最大长度一样多。这有点 hack，和 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 函数。)