在mysql函数中循环遍历JSON对象 [英] Loop through JSON object in mysql function

问题描述

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

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.

这是我的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读取数据

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

现在有这些行

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

我得到内部产品json和第0个产品的ID.但是我想要一种遍历所有产品的方法.

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.

推荐答案

您可以将WHILE循环与JSON_LENGTH结合使用来实现此目的:

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 ;-)

注意:5.7.8中添加了MySQL JSON函数，因此您需要首先检查MySQL版本.

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

这篇关于在mysql函数中循环遍历JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！