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 |