Stored Procedure How to Insert Multiple rows into DB from a list of lists

Question

I have a MySql DB table as follows:

id | page_name | language | item_id | item_text

The table holds language-based text for use on various pages in a web app. I have the translated text for multiple languages and multiple pages in JSON files 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 includes the data for one particular language and one particular web page.

I have a large amount of data to insert and I am trying to find a way to efficiently add this data into my DB. I'm assuming a stored procedure that would be the best way but I am struggling to find any tutorials that cover looping though arrays like this and inserting.

Is it possible to feed an array like this into a stored proc and loop through it, inserting each row?

Any help would be much appreciated.

Answer 1

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;

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