By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab( jsdata JSON );
CREATE TABLE business( title VARCHAR(100), address VARCHAR(100), website VARCHAR(100), page VARCHAR(100) );
CREATE TABLE business_phones( business_title VARCHAR(100), phone_number VARCHAR(100), phone_name VARCHAR(100) );
INSERT INTO tab VALUES('[
{
"title": "CONSERVE IT LTD",
"address": "12 Truman Ave (10) ",
"phones": [
{
"name": "telephone_1",
"number": "876-754-0220"
},
{
"name": "telephone_2",
"number": "876-754-0221"
}
],
"website": "www.conserveitja.com",
"page": 1
},
{
"title": "Consie Walters Cancer Hospital",
"address": "22 Deanery Rd (3) ",
"phones": [
{
"name": "telephone_1",
"number": "876-930-5016"
}
],
"page": 1
}]');
INSERT INTO business(title, address, website, page)
SELECT t.*
FROM tab
CROSS JOIN
JSON_TABLE(jsdata, '$[*]' COLUMNS (
title VARCHAR(100) PATH '$.title',
address VARCHAR(100) PATH '$.address',
website VARCHAR(100) PATH '$.website',
page VARCHAR(100) PATH '$.page')
) t
Records: 2 Duplicates: 0 Warnings: 0
INSERT INTO business_phones(business_title, phone_number, phone_name)
SELECT t.*
FROM tab
CROSS JOIN
JSON_TABLE(jsdata, '$[*]' COLUMNS (
business_title VARCHAR(100) PATH '$.title',
NESTED PATH '$.phones[*]' COLUMNS (
phone_number VARCHAR(100) PATH '$.number',
phone_name VARCHAR(100) PATH '$.name')
)
) t
Records: 3 Duplicates: 0 Warnings: 0
SELECT * FROM business;
| title | address | website | page |
|---|---|---|---|
| CONSERVE IT LTD | 12 Truman Ave (10) | www.conserveitja.com | 1 |
| Consie Walters Cancer Hospital | 22 Deanery Rd (3) | null | 1 |
SELECT * FROM business_phones;
| business_title | phone_number | phone_name |
|---|---|---|
| CONSERVE IT LTD | 876-754-0220 | telephone_1 |
| CONSERVE IT LTD | 876-754-0221 | telephone_2 |
| Consie Walters Cancer Hospital | 876-930-5016 | telephone_1 |