将MySQL中的JSON数组转换为行

I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.

我喜欢MySQL 5.7中的新JSON函数，但遇到了试图将JSON中的值合并到普通表结构中的块。

Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.

从中获取JSON，操作和提取数组等很简单。一路JSON_EXTRACT。但是从JSON数组到行的逆转呢？也许我对现有的MySQL JSON功能非常密切，但我还是无法解决这个问题。

For example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc and union them together.

例如，假设我有一个JSON数组，并希望为数组中的每个元素插入一行及其值？我找到的唯一方法是编写一堆JSON_EXTRACT（...'$ [0]'）JSON_EXTRACT（...'$ [1]'）等并将它们合并在一起。

Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string?

或者，说我有一个JSON数组，并希望GROUP_CONCAT（）它到一个逗号分隔的字符串？

In other words, I know I can do this:

换句话说，我知道我可以这样做：

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);

But that hurts my eyes. And my heart.

但这伤害了我的眼睛。而我的心。

How can I do something like:

我怎么能这样做：

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

... and have it concatenate together the values in the array vs. the JSON array itself?

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

I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of:

我想我在这里寻找的是某种JSON_SPLIT：

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

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

If MySQL had a proper STRING_SPLIT(val, 'separator') table returning function, I could hack it (escaping be damned), but that's not available either.

如果MySQL有一个正确的STRING_SPLIT（val，'separator'）表返回函数，我可以破解它（逃避被诅咒），但那也不可用。

3 个解决方案

#1

It's true that it's not a good idea to denormalize into JSON, but sometimes you need to deal with JSON data, and there's a way to extract a JSON array into rows in a query.

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

The trick is to perform a join on a temporary or inline table of indexes, which gives you a row for each non-null value in a JSON array. I.e., if you have a table with values 0, 1, and 2 that you join to a JSON array “fish” with two entries, then fish[0] matches 0, resulting in one row, and fish[1] matches 1, resulting in a second row, but fish[2] is null so it doesn't match the 2 and doesn't produce a row in the join. You need as many numbers in the index table as the max length of any array in your JSON data. It's a bit of a hack, and it's about as painful as the OP's example, but it's very handy.

诀窍是在临时或内联索引表上执行连接，这为JSON数组中的每个非空值提供了一行。即，如果你有一个值为0,1和2的表，你加入一个带有两个条目的JSON数组“fish”，那么fish [0]匹配0，结果是一行，fish [1]匹配1，导致第二行，但fish [2]为null，因此它与2不匹配，并且在连接中不产生行。索引表中需要的数字与JSON数据中任何数组的最大长度一样多。这有点像黑客，它和OP的例子一样痛苦，但它非常方便。

Example (requires MySQL 5.7.8 or later):

示例（需要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;

The result is:

结果是：

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

It looks like the MySQL team may add a JSON_TABLE function in MySQL 8 to make all this easier. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/)

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

#2

In My Case, JSON Function was not available so I used a hack. As mentioned by Chris MYSQL do not have STRING_SPLIT but it does have substring_index.

在我的情况下，JSON功能不可用所以我使用了黑客。正如Chris MYSQL所提到的，没有STRING_SPLIT但它确实有substring_index。

For the input

用于输入

{
    "requestId":"BARBH17319901529",
    "van":"0xxxxx91317508",
    "source":"AxxxS",
    "txnTime":"15-11-2017 14:08:22"
}

You can use:

您可以使用：

trim(
    replace(
        substring_index(
            substring(input, 
                locate('requestid',input) 
                    + length('requestid') 
                    + 2), ',', 1), '"', '')
) as Requestid`

The output will be:

输出将是：

BARBH17319901529

You can modify according to your requirement.

您可以根据您的要求进行修改。

#3

I was working in a report where there was a big json array list in one column. I modified the datamodel to store the relationship 1 to * instead of storing everything in one single column. For doing this process, I had to use a while in a stored procedure since I do not know the maximum size:

我在一份报告中工作，其中一列中有一个大的json数组列表。我修改了数据模型以将关系1存储到*而不是将所有内容存储在一个列中。为了完成这个过程，我不得不在存储过程中使用一段时间，因为我不知道最大大小：

DROP PROCEDURE IF EXISTS `test`;

DELIMITER #

CREATE PROCEDURE `test`()
PROC_MAIN:BEGIN
DECLARE numNotes int;
DECLARE c int;
DECLARE pos varchar(10);

SET c = 0;
SET numNotes = (SELECT 
ROUND (   
        (
            LENGTH(debtor_master_notes)
            - LENGTH( REPLACE ( debtor_master_notes, "Id", "") ) 
        ) / LENGTH("Id")        
    ) AS countt FROM debtor_master
order by countt desc Limit 1);

DROP TEMPORARY TABLE IF EXISTS debtorTable;
CREATE TEMPORARY TABLE debtorTable(debtor_master_id int(11), json longtext, note int);
WHILE(c <numNotes) DO
SET pos = CONCAT('$[', c, ']');
INSERT INTO debtorTable(debtor_master_id, json, note)
SELECT debtor_master_id, JSON_EXTRACT(debtor_master_notes, pos), c+1
FROM debtor_master
WHERE debtor_master_notes IS NOT NULL AND debtor_master_notes like '%[%' AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL;
SET c = c + 1;
END WHILE;
SELECT * FROM debtorTable;
END proc_main #

DELIMITER ;

#1