Schreiben Sie den Titel wie folgt um: Konvertieren Sie das JSON-Array in Zeilen in MySQL

Question

Update: Dies ist jetzt in MySQL 8 über die JSON_TABLE-Funktion möglich: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions . html< /p>

Mir gefallen die neuen JSON-Funktionen in MySQL 5.7, aber ich habe Probleme beim Versuch, Werte aus JSON in eine normale Tabellenstruktur zusammenzuführen.

JSON abrufen, bearbeiten, Arrays daraus extrahieren und vieles mehr ist ganz einfach. JSON_EXTRACT den ganzen Weg. Aber wie sieht es umgekehrt aus, vom JSON-Array zur Zeile? Vielleicht interessiere ich mich für die vorhandene MySQL-JSON-Funktionalität, aber ich konnte das nicht herausfinden.

Angenommen, ich habe ein JSON-Array und möchte für jedes Element im Array und seinen Wert eine Zeile einfügen? Die einzige Möglichkeit, die ich gefunden habe, besteht darin, eine Reihe von JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') usw. zu schreiben und sie zusammenzufügen.

Angenommen, ich habe alternativ ein JSON-Array und möchte es mit GROUP_CONCAT() in eine einzelne durch Kommas getrennte Zeichenfolge umwandeln?

Mit anderen Worten, ich weiß, dass ich das kann:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  AUS
  (
    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);

Aber es tut meinen Augen weh. Und mein Herz.

Wie kann ich so etwas machen:

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

...und die Werte im Array mit dem JSON-Array selbst verketten?

Ich denke, was ich hier suche, ist eine Art JSON_SPLIT, etwa:

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

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

Wenn MySQL eine richtige STRING_SPLIT(val, 'separator')-Tabellenrückgabefunktion hätte, könnte ich sie hacken (die verdammte Escape-Funktion), aber das funktioniert auch nicht.

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;