当数组中的json对象数量未知时，如何从mysql文本列中提取json数组作为表格?

本文介绍了当数组中的json对象数量未知时，如何从mysql文本列中提取json数组作为表格?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

是否有一种方法可以从包含带有json对象数量不同的json数组的文本列中提取数据到表中?

Is there a way to extract data from a text column containing json arrays with a varying number of json objects into a table?

例如，如果我...

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:

I am able to extract tableWithJsonStr.jsonStr by running:

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中添加另一个SELECT 4 AS idx，否则不会考虑它们.有没有更好的方法可以遍历数组中不需要预先知道每个数组中可能存在的最大对象数的对象?

While the solution works, it is not extensible. If I have more than 3 objects in my json array, they will not be accounted for unless I add another SELECT 4 AS idx in my JOIN. Is there a better way to iterate over the objects in the array that doesn't require foreknowledge of the max number of objects that might be in each array?

推荐答案

如果您使用的是MySQL 8.0，则可以使用 JSON_TABLE 命令从JSON的每一行提取数据:

If you're using MySQL 8.0, you can use the JSON_TABLE command to extract your data from each row of 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上的演示

如果您坚持使用MySQL 5.7，则可以使用存储过程来提取数据:

If you are stuck with MySQL 5.7, you can use a stored procedure to extract the data:

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上的演示