MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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"}]');

Records: 3  Duplicates: 0  Warnings: 0


CREATE PROCEDURE extract_animals()
BEGIN
  DECLARE idx INT;
  DECLARE finished INT DEFAULT 0;
  DECLARE loc, 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 loc, json;
    IF finished = 1 THEN
      LEAVE json_loop;
    END IF;
    SET idx = 0;
    WHILE JSON_CONTAINS_PATH(json, 'one', CONCAT('$[', idx, ']')) DO
      INSERT INTO animal VALUES(loc,
        idx,
        JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$[', idx, '].animalId'))),
        JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$[', idx, '].type'))),
        JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$[', idx, '].color'))),
        JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$[', idx, '].isPet'))));
      SET idx = idx + 1;
    END WHILE;
  END LOOP json_loop;
END
CALL extract_animals()
SELECT * FROM animal


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

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