sql—当数组中json对象的数目未知时，如何从mysql文本列中提取json数组作为表？

zz2j4svz 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(343)

有没有一种方法可以将包含json数组的文本列中的数据提取到一个表中？
例如，如果我。。。

CREATE TABLE tableWithJsonStr (location TEXT, jsonStr TEXT);

INSERT INTO tableWithJsonStr VALUES 
('Home', '[{"animalId":"1","type":"dog", "color":"white","isPet":"1"},{"animalId":"2","type":"cat", "color":"brown","isPet":"1"}]'),
('Farm', '[{"animalId":"8","type":"cow", "color":"brown","isPet":"0"}, {"animalId":"33","type":"pig", "color":"pink","isPet":"0"}, {"animalId":"22","type":"horse", "color":"black","isPet":"1"}]'),
('Zoo', '[{"animalId":"5","type":"tiger", "color":"stripes","isPet":"0"}]');

和

CREATE TABLE animal (
  location TEXT,
  idx INT,
  animalId INT,
  type TEXT,
  color TEXT,
  isPet BOOLEAN
);

我可以通过运行以下命令提取tablewithjsonstr.jsonstr：

INSERT INTO animal
SELECT location,
       idx AS id,
       TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].animalId'))) AS animalId,
       TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].type'))) AS type,
       TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].color'))) AS color,
       TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].isPet'))) AS isPet
FROM tableWithJsonStr
JOIN(
  SELECT 0 AS idx UNION
         SELECT 1 AS idx UNION
         SELECT 2 AS idx UNION
         SELECT 3 AS idx
  ) AS indexes
WHERE JSON_EXTRACT(jsonStr, CONCAT('$[', idx, ']')) IS NOT NULL;

动物表的结果是：

| location | idx | animalId | type  | color   | isPet |
|==========|=====|==========|=======|=========|=======|
| Farm     |   0 |        8 |  cow  |   brown |     0 |
| Farm     |   1 |       33 |  pig  |    pink |     0 |
| Farm     |   2 |       22 | horse |   black |     1 |
| Home     |   0 |        1 |   dog |   white |     1 |
| Home     |   1 |        2 |   cat |   brown |     1 |
| Zoo      |   0 |        5 | tiger | stripes |     0 |

虽然解决方案可行，但它是不可扩展的。如果我的json数组中有3个以上的对象，除非我在join中添加另一个select4作为idx，否则它们不会被计算在内。有没有更好的方法来迭代数组中的对象，而不需要预先知道每个数组中可能存在的最大对象数？

sql mysql JSON Arrays Extract

来源：https://stackoverflow.com/questions/54119149/how-to-extract-json-array-as-table-from-mysql-text-column-when-number-of-json-ob

1条答案

按热度按时间

rbpvctlc1#

如果您使用的是mysql 8.0，那么可以使用 JSON_TABLE 命令从 JSON :

SELECT t1.location, farm.*
FROM tableWithJsonStr t1
JOIN JSON_TABLE(t1.jsonStr,
     '$[*]'
     COLUMNS (idx FOR ORDINALITY,
              animalId INT PATH '$.animalId',
              type TEXT PATH '$.type',
              color TEXT PATH '$.color',
              isPet BOOLEAN PATH '$.isPet')
     ) farm
ORDER BY location, idx

输出：

location    idx     animalId    type    color       isPet
Farm        1       8           cow     brown       0
Farm        2       33          pig     pink        0
Farm        3       22          horse   black       1
Home        1       1           dog     white       1
Home        2       2           cat     brown       1
Zoo         1       5           tiger   stripes     0

在dbfiddle上演示
如果您仍然使用MySQL5.7，可以使用存储过程来提取数据：

DELIMITER $$
CREATE PROCEDURE extract_animals()
BEGIN
  DECLARE idx INT;
  DECLARE finished INT DEFAULT 0;
  DECLARE location, json VARCHAR(200);
  DECLARE json_cursor CURSOR FOR SELECT location, jsonStr FROM tableWithJsonStr;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  DROP TABLE IF EXISTS animal;
  CREATE TABLE animal (location TEXT, idx INT, animalId INT, type TEXT, color TEXT, isPet BOOLEAN);
  OPEN json_cursor;
  json_loop: LOOP
    FETCH json_cursor INTO location, json;
    IF finished = 1 THEN
      LEAVE json_loop;
    END IF;
    SET idx = 0
    WHILE JSON_CONTAINS_PATH(json, 'one', CONCAT('$[', idx, ']))
      INSERT INTO animal VALUES(location,
       idx,
       JSON_UNQUOTE(JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].animalId'))),
       JSON_UNQUOTE(JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].type'))),
       JSON_UNQUOTE(JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].color'))),
       JSON_UNQUOTE(JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].isPet')));
      SET idx = idx + 1;
    END WHILE;
  END LOOP json_loop;
END $$

输出：

location    idx     animalId    type    color       isPet
Home        0       1           dog     white       1
Home        1       2           cat     brown       1
Farm        0       8           cow     brown       0
Farm        1       33          pig     pink        0
Farm        2       22          horse   black       1
Zoo         0       5           tiger   stripes     0

在dbfiddle上演示

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

我来回答

sql—当数组中json对象的数目未知时，如何从mysql文本列中提取json数组作为表？

1条答案

相关问题

热门标签

最新问答