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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With