By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (name VARCHAR(255),
position VARCHAR(255),
city VARCHAR(255),
postcode INT,
entered_at DATE,
income INT);
SET @data := '
{
"data": [
[
"Tiger Nixon",
"System Architect",
"Edinburgh",
"5421",
"2011/04/25",
"$320,800"
],
[
"Garrett Winters",
"Accountant",
"Tokyo",
"8422",
"2011/07/25",
"$170,750"
],
[
"Ashton Cox",
"Junior Technical Author",
"San Francisco",
"1562",
"2009/01/12",
"$86,000"
],
[
"Cedric Kelly",
"Senior Javascript Developer",
"Edinburgh",
"6224",
"2012/03/29",
"$433,060"
],
[
INSERT INTO test (name, position, city, postcode, entered_at, income)
SELECT data ->> '$[0]' name,
data ->> '$[1]' position,
data ->> '$[2]' city,
data ->> '$[3]' postcode,
STR_TO_DATE(data ->> '$[4]', '%Y\/%m\/%d') entered_at,
REPLACE(SUBSTRING(data ->> '$[5]' FROM 2), ',', '') income
FROM JSON_TABLE(@data,
'$.data[*]' COLUMNS (data JSON PATH '$')) jsontable
Records: 57 Duplicates: 0 Warnings: 0
SELECT * FROM test
| name | position | city | postcode | entered_at | income |
|---|---|---|---|---|---|
| Tiger Nixon | System Architect | Edinburgh | 5421 | 2011-04-25 | 320800 |
| Garrett Winters | Accountant | Tokyo | 8422 | 2011-07-25 | 170750 |
| Ashton Cox | Junior Technical Author | San Francisco | 1562 | 2009-01-12 | 86000 |
| Cedric Kelly | Senior Javascript Developer | Edinburgh | 6224 | 2012-03-29 | 433060 |
| Airi Satou | Accountant | Tokyo | 5407 | 2008-11-28 | 162700 |
| Brielle Williamson | Integration Specialist | New York | 4804 | 2012-12-02 | 372000 |
| Herrod Chandler | Sales Assistant | San Francisco | 9608 | 2012-08-06 | 137500 |
| Rhona Davidson | Integration Specialist | Tokyo | 6200 | 2010-10-14 | 327900 |
| Colleen Hurst | Javascript Developer | San Francisco | 2360 | 2009-09-15 | 205500 |
| Sonya Frost | Software Engineer | Edinburgh | 1667 | 2008-12-13 | 103600 |
| Jena Gaines | Office Manager | London | 3814 | 2008-12-19 | 90560 |
| Quinn Flynn | Support Lead | Edinburgh | 9497 | 2013-03-03 | 342000 |
| Charde Marshall | Regional Director | San Francisco | 6741 | 2008-10-16 | 470600 |
| Haley Kennedy | Senior Marketing Designer | London | 3597 | 2012-12-18 | 313500 |
| Tatyana Fitzpatrick | Regional Director | London | 1965 | 2010-03-17 | 385750 |
| Michael Silva | Marketing Designer | London | 1581 | 2012-11-27 | 198500 |
| Paul Byrd | Chief Financial Officer (CFO) | New York | 3059 | 2010-06-09 | 725000 |
| Gloria Little | Systems Administrator | New York | 1721 | 2009-04-10 | 237500 |
| Bradley Greer | Software Engineer | London | 2558 | 2012-10-13 | 132000 |
| Dai Rios | Personnel Lead | Edinburgh | 2290 | 2012-09-26 | 217500 |
| Jenette Caldwell | Development Lead | New York | 1937 | 2011-09-03 | 345000 |
| Yuri Berry | Chief Marketing Officer (CMO) | New York | 6154 | 2009-06-25 | 675000 |
| Caesar Vance | Pre-Sales Support | New York | 8330 | 2011-12-12 | 106450 |
| Doris Wilder | Sales Assistant | Sydney | 3023 | 2010-09-20 | 85600 |
| Angelica Ramos | Chief Executive Officer (CEO) | London | 5797 | 2009-10-09 | 1200000 |
| Gavin Joyce | Developer | Edinburgh | 8822 | 2010-12-22 | 92575 |
| Jennifer Chang | Regional Director | Singapore | 9239 | 2010-11-14 | 357650 |
| Brenden Wagner | Software Engineer | San Francisco | 1314 | 2011-06-07 | 206850 |
| Fiona Green | Chief Operating Officer (COO) | San Francisco | 2947 | 2010-03-11 | 850000 |
| Shou Itou | Regional Marketing | Tokyo | 8899 | 2011-08-14 | 163000 |
| Michelle House | Integration Specialist | Sydney | 2769 | 2011-06-02 | 95400 |
| Suki Burks | Developer | London | 6832 | 2009-10-22 | 114500 |
| Prescott Bartlett | Technical Author | London | 3606 | 2011-05-07 | 145000 |
| Gavin Cortez | Team Leader | San Francisco | 2860 | 2008-10-26 | 235500 |
| Martena Mccray | Post-Sales support | Edinburgh | 8240 | 2011-03-09 | 324050 |
| Unity Butler | Marketing Designer | San Francisco | 5384 | 2009-12-09 | 85675 |
| Howard Hatfield | Office Manager | San Francisco | 7031 | 2008-12-16 | 164500 |
| Hope Fuentes | Secretary | San Francisco | 6318 | 2010-02-12 | 109850 |
| Vivian Harrell | Financial Controller | San Francisco | 9422 | 2009-02-14 | 452500 |
| Timothy Mooney | Office Manager | London | 7580 | 2008-12-11 | 136200 |
| Jackson Bradshaw | Director | New York | 1042 | 2008-09-26 | 645750 |
| Olivia Liang | Support Engineer | Singapore | 2120 | 2011-02-03 | 234500 |
| Bruno Nash | Software Engineer | London | 6222 | 2011-05-03 | 163500 |
| Sakura Yamamoto | Support Engineer | Tokyo | 9383 | 2009-08-19 | 139575 |
| Thor Walton | Developer | New York | 8327 | 2013-08-11 | 98540 |
| Finn Camacho | Support Engineer | San Francisco | 2927 | 2009-07-07 | 87500 |
| Serge Baldwin | Data Coordinator | Singapore | 8352 | 2012-04-09 | 138575 |
| Zenaida Frank | Software Engineer | New York | 7439 | 2010-01-04 | 125250 |
| Zorita Serrano | Software Engineer | San Francisco | 4389 | 2012-06-01 | 115000 |
| Jennifer Acosta | Junior Javascript Developer | Edinburgh | 3431 | 2013-02-01 | 75650 |
| Cara Stevens | Sales Assistant | New York | 3990 | 2011-12-06 | 145600 |
| Hermione Butler | Regional Director | London | 1016 | 2011-03-21 | 356250 |
| Lael Greer | Systems Administrator | London | 6733 | 2009-02-27 | 103500 |
| Jonas Alexander | Developer | San Francisco | 8196 | 2010-07-14 | 86500 |
| Shad Decker | Regional Director | Edinburgh | 6373 | 2008-11-13 | 183000 |
| Michael Bruce | Javascript Developer | Singapore | 5384 | 2011-06-27 | 183000 |
| Donna Snider | Customer Support | New York | 4226 | 2011-01-25 | 112000 |