title: MYSQL Stored Procedure to Iterate Json Array Data
body: <p>I have a couple of columns that are json arrays that have datetime data like this:</p>
  
  <pre><code>["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"]
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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 &lt; 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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>set @i = 0;
  
  select JSON_EXTRACT(associated_usages, CONCAT('$[',@i,']')) from usagesTbl where associated_usages not like '[]';
  </code></pre>
  
  <p>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!</p>