MYSQL Stored Procedure to Iterate Json Array Data

Question

I have a couple of columns that are json arrays that have datetime data like this:

["2017-04-18 11:05:00.000000"]
["2017-04-20 11:05:00.000000"]
["2017-04-22 11:05:00.000000"]
["2017-12-11 22:14:02.000000", "2017-12-11 22:14:08.000000", "2017-12-11 22:19:13.000000", "2017-12-11 22:20:44.000000", "2017-12-11 22:21:54.000000", "2017-12-11 22:23:09.000000"]
["2017-12-13 13:21:04.000000"]
["2017-12-14 13:10:44.000000", "2017-12-14 13:21:51.000000"]
["2017-12-15 13:27:21.000000", "2017-12-15 13:30:21.000000"]
["2017-12-16 15:15:22.000000"]

The goal is to parse out the datetime data and store it into a separate table from which I plan on doing some fun stuff. Currently, it only inserts the first record only, and it inserts it ~180000 times. My current code is:

BEGIN
DECLARE finished INTEGER DEFAULT 0; 
DECLARE i INTEGER DEFAULT 0;
DECLARE usages VARCHAR(4000);

-- declare cursor for employee email
DEClARE curUsages
    CURSOR FOR 
        SELECT associated_usages from usagesTbl where associated_usages not like '[]';

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;

OPEN curUsages;

getUsages: LOOP
    FETCH curUsages INTO usages;
    IF finished = 1 THEN 
        LEAVE getUsages;
    END IF;
    WHILE i < JSON_LENGTH(usages) DO
        INSERT INTO usagesTbl VALUES (JSON_EXTRACT(usages, CONCAT('$[',i,']')));
        SET i = i + 1;
    END WHILE;
    SET i = 0;
END LOOP getUsages;
CLOSE curUsages;
END;

it seems that the while loop variable "i" is not increasing, and I am getting constantly stuck in the loop. The reason for me thinking this is that I pulled out the JSON_EXTRACT code and wrote this for testing:

set @i = 0;

select JSON_EXTRACT(associated_usages, CONCAT('$[',@i,']')) from usagesTbl where associated_usages not like '[]';

I can change the value of @i to whatever index I want and I get the right data. Im just stuck on why it doesn't work in the while loop during the stored procedure. Any help is greatly appreciated!

Answer 1

Fixed it! It somehow created an infinite loop that just kept on inserting data even when the stored proc said it was done running. I dropped and recreated the table, and changed the datatype of usages back from VARCHAR to json, and it worked like a charm.