How to insert data from multiple nested lists into multiple rows in the database using a stored procedure

Question

I have a MySql database table as shown below:

id | page_name | language | item_id | item_text

This table stores language-based text used for various pages in a web application. I have translated text for multiple languages ​​and multiple pages, stored in a JSON file in the following format:

[
    ["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"]
]

Each JSON document contains data for a specific language and a specific web page.

I have a lot of data to insert and I'm trying to find an efficient way to add this data to my database. I'm assuming using a stored procedure is the best way, but I can't find any tutorials that cover looping through an array like this and inserting.

Is it possible to pass such an array to a stored procedure and loop through it, inserting row by row?

Any help would be greatly appreciated.

Answer 1

创建表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;

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

fiddle