title: Loop through JSON object in mysql function
body: <p>I have a json object which has list of products under a bill. I want to write a mysql function for it which reads the data from the json and iterates over it one by one and inserts the same data to product and bill tables. </p>
  
  <p>Here is my json object</p>
  
  <pre><code>{"billNo":16,"date":"2017-13-11 09:05:01","customerName":"Vikas","total":350.0,"fixedCharges":100,"taxAmount":25.78,"status":paid,"product":[{"productId":"MRR11","categoryId":72,"categoryName":"Parker Pen","cost":200,"quantity":2,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}},{"productId":"MRR12","categoryId":56,"categoryName":"Drawing Books","cost":150,"quantity":3,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}}]}
  </code></pre>
  
  <p>Here I have a mysql function which reads the data from the JSON</p>
  
  <pre><code>CREATE DEFINER=`mydb`@`%` FUNCTION `raiseOrder`(dataObject Json) 
  RETURNS bigint(11)
  BEGIN
      DECLARE billNo BIGINT(11) DEFAULT NULL;
      DECLARE customerName VARCHAR(64);
      DECLARE date datetime DEFAULT NOW();
      DECLARE total Float(12,2);
      DECLARE taxamt Float(12,2);
      DECLARE fixedCharges Float(12,2);
  
      DECLARE products json;
      DECLARE productId bigint(15) DEFAULT NULL;
      DECLARE categoryId bigint(11);
      DECLARE cost float;
      DECLARE categoryName varchar(64);
      DECLARE quantity int default 0;
      DECLARE supplierId bigint(11);
      DECLARE supplierName varchar(128);
  
      SET billNo = (SELECT JSON_EXTRACT(dataObject, "$.billNo"));
      SET customerName = (SELECT JSON_EXTRACT(dataObject, "$.customerName"));
      SET products = (SELECT JSON_EXTRACT(dataObject, "$.products"));        
      SET productId = (SELECT JSON_EXTRACT(products, "$[0].productId"));      
  RETURN 1;
  END
  </code></pre>
  
  <p>Now with these lines </p>
  
  <pre><code>SET products = (SELECT JSON_EXTRACT(dataObject, "$.products"));        
  SET productId = (SELECT JSON_EXTRACT(products, "$[0].productId"));      
  </code></pre>
  
  <p>I get the inner products json and the id of the 0th product. But I want a way to iterate over the array of the products. </p>