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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在mysql函数中循环遍历JSON对象，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2607字，纯文字阅读大概需要4分钟。

内容图文

我有一个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.但是我想要一种迭代产品系列的方法.

解决方法:

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

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;

不过,您可能需要做的不只是“选择产品” ；-)

注意：MySQL JSON函数已在5.7.8中添加,因此您需要首先检查MySQL版本.

内容总结

以上是互联网集市为您收集整理的在mysql函数中循环遍历JSON对象全部内容，希望文章能够帮你解决在mysql函数中循环遍历JSON对象所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---