将标题重写为：将MySQL中的JSON数组转换为行

Question

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

我喜欢 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，从而生成一行，并且 Fish1 匹配 1，导致第二行，但fish[2]为 null，因此它与 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 函数。）

Answer 2

以下是如何使用 JSON_TABLE 在 MySQL 8+ 中：

SELECT *
     FROM
       JSON_TABLE(
         '[5, 6, 7]',
         "$[*]"
         COLUMNS(
           Value INT PATH "$"
         )
       ) data;

您还可以将其用作 MySQL 所缺少的通用字符串分割函数（类似于 PG 的 regexp_split_to_table 或 MSSQL 的 STRING_SPLIT），方法是获取分隔字符串并将其转换为 JSON 字符串：

set @delimited = 'a,b,c';

SELECT *
     FROM
       JSON_TABLE(
         CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
         "$[*]"
         COLUMNS(
           Value varchar(50) PATH "$"
         )
       ) data;