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 TABLE animal (
  location TEXT,
  idx INT,
  animalId INT,
  type TEXT,
  color TEXT,
  isPet BOOLEAN
);
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

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