將標題重寫為：將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;