Loop through JSON object in mysql function

Question

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.

Here is my json object

{"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"}}]}

Here I have a mysql function which reads the data from the JSON

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

Now with these lines

SET products = (SELECT JSON_EXTRACT(dataObject, "$.products"));        
SET productId = (SELECT JSON_EXTRACT(products, "$[0].productId"));      

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.

Solution

You can use a WHILE loop in conjunction with JSON_LENGTH to achieve this:

DECLARE json, products, product VARCHAR(4000);
DECLARE i INT DEFAULT 0;
SELECT '{"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"}}]}
' INTO json;

SELECT json->"$.product" INTO products;

WHILE i < JSON_LENGTH(products) DO
    SELECT JSON_EXTRACT(products,CONCAT('$[',i,']')) INTO product;
    SELECT product;
    SELECT i + 1 INTO i;
END WHILE;

You'll probably need to do more than simply 'SELECT product' though ;-)

NOTE: MySQL JSON functions were added in 5.7.8 so you'll need to check your MySQL version first.