CODEWITHSUNDEEP
mysqlsqljsonmariadb
M.mhr
M.mhr

Reputation: 1749

Insert records to MySQL table from JSONArray

I have a table as :

CREATE TABLE `mydb`.`mytable` (
  `id` INT NOT NULL,
  `name` VARCHAR(50) NULL,
  PRIMARY KEY (`id`));

and a JASONArray like this :

[{"id":1, "name":"X"}, {"id":2, "name":"Y"},{"id":3,"name":"Z"}]

Is it possible to extract each JSONObject from array and insert it to table ? or something like this :

Insert Into mytable Select From JSON_EXTRACT(@JsonArray, '?')

What I should use instead of '?' mark in JASON_Extract function ?

I have a Stored Procedure with a Varchar parameter, I want to pass my JSONArray as a String to this param and insert each object as a row in table

Upvotes: 1

Views: 2680

Answers (1)

markusjm
markusjm

Reputation: 2562

You can do it by using JSON_VALUE and looping over the array:

CREATE OR REPLACE TABLE t1(id INT, name VARCHAR(100));

DELIMITER // ;

CREATE OR REPLACE PROCEDURE p(IN json TEXT)
BEGIN
    -- Our loop variable                                                                                                                                                                      
    DECLARE i INT DEFAULT 0;
    -- How long our JSON array is                                                                                                                                                             
    DECLARE e INT DEFAULT JSON_LENGTH(json);

    WHILE i < e DO
        -- Get the values at array offset `i` and insert them into the table                                                                                                                  
        INSERT INTO t1 VALUES (JSON_VALUE(json, CONCAT('$[', i, '].id')), JSON_VALUE(json, CONCAT('$[', i, '].name')));
        -- Increment the loop variable                                                                                                                                                        
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ; //

-- Call the procedure and examine results                                                                                                                                                     
SET @json := '[{"id":1, "name":"X"}, {"id":2, "name":"Y"},{"id":3,"name":"Z"}]';
CALL p(@json);
SELECT * FROM t1;

Another option is to use the SEQUENCE engine and INSERT SELECT:

INSERT INTO t1 SELECT
       JSON_VALUE(@json, CONCAT('$[', seq, '].id')) AS id,
       JSON_VALUE(@json, CONCAT('$[', seq, '].name')) AS name
FROM seq_0_to_1000000
WHERE seq < JSON_LENGTH(@json);

The use of seq_0_to_1000000 is not very neat. With MariaDB, you could use the compound statements outside of stored procedures.

Upvotes: 1

Related Questions