将 MySQL 中的 JSON 数组转换为行

Question

提问by Chris Hynes

UPDATE:This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

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

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') 表返回函数，我可以破解它（该死的逃避），但这也不可用。

Answer 1

采纳答案by Chris Hynes

Here's how to do this with JSON_TABLEin MySQL 8+:

以下是在 MySQL 8+ 中使用JSON_TABLE执行此操作的方法：

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

You can also use this as a general string split function which MySQL otherwise lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking a delimited string and turning it into a JSON string:

您还可以将它用作 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;

Answer 2

回答by JimTheFrog

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 fish1matches 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] 为空，因此它与 2 不匹配，并且不会在连接中生成一行。您需要索引表中的数字与 JSON 数据中任何数组的最大长度一样多。这有点像 hack，它和 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_TABLEfunction in MySQL 8 to make all this easier. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/)~~(The MySQL team hasadded a JSON_TABLEfunction.)

~~看起来 MySQL 团队可能会JSON_TABLE在 MySQL 8 中添加一个函数来简化这一切。（http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/）~~（MySQL的团队已经增加了一个JSON_TABLE功能。）

Answer 3

回答by wa56

In 2018. What I do for this case.

在 2018 年。我为这个案子做了什么。

Prepare a table with just continually number in rows.

CREATE TABLE `t_list_row` (
`_row` int(10) unsigned NOT NULL,
PRIMARY KEY (`_row`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;

Enjoy easy JSON array to rows in the future.

SET @j = '[1, 2, 3]';
SELECT 
JSON_EXTRACT(@j, CONCAT('$[', B._row, ']'))
FROM (SELECT @j AS B) AS A
INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);

准备一个表格，行中只有连续编号。

CREATE TABLE `t_list_row` (
`_row` int(10) unsigned NOT NULL,
PRIMARY KEY (`_row`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;

将来享受简单的 JSON 数组到行。

SET @j = '[1, 2, 3]';
SELECT 
JSON_EXTRACT(@j, CONCAT('$[', B._row, ']'))
FROM (SELECT @j AS B) AS A
INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);

For this way. is some kind like 'Chris Hynes' way. but you don't need to know array size.

对于这种方式。有点像“克里斯·海因斯”的方式。但您不需要知道数组大小。

Good: Clear, short, easy code, no need to know array size, no loop, no invoke other function will be fast.

优点：清晰、简短、简单的代码，不需要知道数组大小，没有循环，没有调用其他函数会很快。

Bad: You need one more table with enough rows.

不好：您还需要一张有足够行数的表。

Answer 4

回答by Vishal Gupta

In My Case, JSONFunction was not available so I used a hack. As mentioned by Chris MYSQL do not have STRING_SPLITbut it does have substring_index.

在我的案例中，JSON函数不可用，所以我使用了 hack。正如 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.

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

Answer 5

回答by Nino ?kopac

Create a new table pseudo_rowswith values from 0 until 99 - these will be used as keys (if your array has more than a hundred values, add more values into pseudo_rows).

创建一个pseudo_rows值从 0 到 99的新表- 这些将用作键（如果您的数组有一百多个值，则向中添加更多值pseudo_rows）。

NOTE: If you're running MariaDB, you can skip this and simply use pseudo sequence tables (e.g. seq_0_to_99).

注意：如果您正在运行 MariaDB，则可以跳过此步骤而只需使用伪序列表（例如seq_0_to_99）。

CREATE TABLE `pseudo_rows` (
  `row` int(10) unsigned NOT NULL,
  PRIMARY KEY (`row`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT pseudo_rows VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92), (93), (94), (95), (96), (97), (98), (99)

For this example, I'll be using a table eventswhich stores groups of artists:

在这个例子中，我将使用一个events存储艺术家组的表：

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `artists` json DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `events` (`id`, `artists`) VALUES ('1', '[{\"id\": 123, \"name\": \"Pink Floyd\"}]');
INSERT INTO `events` (`id`, `artists`) VALUES ('2', '[{\"id\": 456, \"name\": \"Nirvana\"}, {\"id\": 789, \"name\": \"Eminem\"}]');

The query to get all artists, one per row, is as follows:

获取所有艺术家（每行一个）的查询如下：

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(events.artists, CONCAT('$[', pseudo_rows.row, '].name'))) AS performer
FROM events
JOIN pseudo_rows
HAVING performer IS NOT NULL

And the resultset is:

结果集是：

performer
---------
Pink Floyd
Nirvana
Eminem

Answer 6

回答by Exec21

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 ;

将 MySQL 中的 JSON 数组转换为行

提问by Chris Hynes

采纳答案by Chris Hynes

回答by JimTheFrog

回答by wa56

回答by Vishal Gupta

回答by Nino ?kopac

回答by Exec21

相关推荐

最近更新

标签

将 MySQL 中的 JSON 数组转换为行

提问by Chris Hynes

采纳答案by Chris Hynes

回答by JimTheFrog

回答by wa56

回答by Vishal Gupta

回答by Nino ?kopac

回答by Exec21

相关推荐

如何为 MySql 表列之一生成唯一的随机字符串？

如何检查 MySQL 中的列是空的还是空的？

MySql 如何在更新语句中设置局部变量（语法？）

MySQL 错误 1698 (28000)：拒绝用户“root”@“localhost”的访问

相关推荐

最近更新

标签