mysql 迭代函数_在mysql函数中循环遍历JSON对象

我有一个JSON对象,其中包含一份帐单下的产品列表.我想为其编写一个mysql函数,该函数从json读取数据并对其进行一个接一个的迭代,并将相同的数据插入到product和bill表中.

这是我的json对象

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

在这里,我有一个mysql函数,它从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

现在有了这些行

SET products = (SELECT JSON_EXTRACT(dataObject, "$.products"));

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

我得到内部产品json和第0个产品的ID.但是我想要一种迭代产品系列的方法.