By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY,
page_name VARCHAR(64),
language CHAR(2),
item_id INT,
item_text VARCHAR(255)
);
SET @data := '
[
["home","fr",1,"French text item 1"],
["home","fr",2,"French text item 2"],
["home","fr",3,"French text item 3"],
["home","fr",4,"French text item 4"],
["home","fr",5,"French text item 5"]
]
';
INSERT INTO test (page_name, language, item_id, item_text)
SELECT page_name, language, item_id, item_text
FROM JSON_TABLE(@data,
'$[*]' COLUMNS ( page_name VARCHAR(64) PATH '$[0]',
language CHAR(2) PATH '$[1]',
item_id INT PATH '$[2]',
item_text VARCHAR(255) PATH '$[3]'
)
) jsontable;
Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM test;
| id | page_name | language | item_id | item_text |
|---|---|---|---|---|
| 1 | home | fr | 1 | French text item 1 |
| 2 | home | fr | 2 | French text item 2 |
| 3 | home | fr | 3 | French text item 3 |
| 4 | home | fr | 4 | French text item 4 |
| 5 | home | fr | 5 | French text item 5 |