将mysql中的json数组转换为行

ezykj2lf 于 2021-06-25 发布在 Mysql

关注(0)|答案(8)|浏览(1306)

更新：在MySQL8中，现在可以通过json表函数：https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
我很喜欢MySQL5.7中新的json函数，但是在尝试将json中的值合并到正常的表结构时遇到了一个块。
获取json、操作和从中提取数组等都很简单。你一直在提取。但是反过来呢，从json数组到行呢？也许我对现有的mysql-json功能很熟悉，但我还没有弄清楚。
例如，假设我有一个json数组，并希望为数组中的每个元素插入一行及其值？我找到的唯一方法就是写一堆json文件（…'$[0]'）json提取（…'$[1] ）等，并将它们结合在一起。
或者，假设我有一个json数组，并想将它组合成一个逗号分隔的字符串？
换句话说，我知道我能做到：

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类型，沿着以下几行进行拆分：

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

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

如果mysql有一个正确的string_split（val，'separator'）表返回函数，我可以破解它（转义是该死的），但这也不可用。

mysql JSON database-normalization

来源：https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows

8条答案

按热度按时间

tcomlyy61#

简单示例：

select subtotal, sku
from t1,
     json_table(t1.refund_line_items,
                '$[*]' columns (
                    subtotal double path '$.subtotal',
                    sku char(50) path '$.line_item.sku'
                    )
         ) refunds

赞(0）回复(0）举报 2021-06-25

gkn4icbw2#

创建新表 pseudo_rows 对于0到99之间的值-这些值将用作键（如果您的数组有超过100个值，请将更多值添加到 pseudo_rows ).
注意：如果您运行的是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)

对于这个例子，我将使用一个表 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\"}]');

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

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

结果集是：

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

赞(0）回复(0）举报 2021-06-25

ssgvzors3#

2018年。我为这个案子做了什么。
准备一张表格，每行只有一个数字。

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

因为这样。有点像克里斯·海恩斯的方式。但是你不需要知道数组的大小。
好：清晰，简短，代码简单，无需知道数组大小，无需循环，无需调用其他函数会很快。
坏：你需要一个足够行的表。

赞(0）回复(0）举报 2021-06-25

gfttwv5a4#

就我而言， JSON 功能不可用，所以我使用了黑客。正如chris提到的，mysql没有 STRING_SPLIT 但它确实有 substring_index .
对于输入

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

您可以使用：

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

输出为：

BARBH17319901529

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

赞(0）回复(0）举报 2021-06-25

kulphzqa5#

下面介绍如何在mysql 8+中使用json表：

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

您还可以将其用作mysql缺少的常规字符串拆分函数（类似于pg的regexp\u split\u to\u表或mssql的string\u split），方法是使用分隔字符串并将其转换为json字符串：

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

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

赞(0）回复(0）举报 2021-06-25

bejyjqdl6#

确实，将json反规范化不是一个好主意，但有时需要处理json数据，有一种方法可以将json数组提取到查询中的行中。
诀窍是在临时索引表或内联索引表上执行联接，它为json数组中的每个非空值提供一行。i、例如，如果有一个表的值为0、1和2，并将其连接到一个json数组“fish”中，该数组有两个条目，那么fish[0]匹配0，得到一行，fish1匹配1，得到第二行，但是fish[2]为null，因此它不匹配2，也不在连接中生成行。索引表中需要的数字与json数据中任何数组的最大长度相同。这有点像黑客，和op的例子一样痛苦，但非常方便。
示例（需要MySQL5.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团队可能会添加 JSON_TABLE mysql 8中的函数使这一切变得更容易(http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/)（mysql团队添加了一个 JSON_TABLE 函数。）

赞(0）回复(0）举报 2021-06-25

ghhkc1vu7#

我在一个报告中工作，其中一列中有一个很大的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 ;

赞(0）回复(0）举报 2021-06-25

gijlo24d8#

如果不能使用json_table函数，但可以使用递归cte，则可以执行以下操作：

SET @j = '[1, 2, 3]';
WITH RECURSIVE x AS (
    /* Anchor, start at -1 in case empty array */
    SELECT -1 AS n

    UNION

    /* Append indexes up to the length of the array */
    SELECT x.n + 1
    FROM x
    WHERE x.n < JSON_LENGTH(@j) - 1
)
/* Use the table of indexes to extract each item and do your GROUP_CONCAT */ 
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']')))
FROM x
/* This prevents selecting from empty array */
WHERE x.n >= 0

这将为每个数组项生成一个由顺序索引组成的表，您可以使用它来使用json\u extract获取值。

赞(0）回复(0）举报 2021-06-25

我来回答

将mysql中的json数组转换为行

8条答案

相关问题

热门标签

最新问答