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

Question

我有一个 JSON 对象，其中包含账单下的产品列表。我想为此编写一个 MySQL 函数，该函数从 JSON 中读取数据，逐一迭代并将相同的数据插入到产品和账单表中。

这是我的 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。但是我希望有一种方法可以遍历产品数组。

我获取了产品的内部信息JSON和第一个产品的ID，但是我想找一种方法来迭代整个产品数组。

- sneha

---

老实说，我不会在MySQL函数中这样做。我会用Python或Ruby编写脚本来完成它。除了MySQL存储过程之外的所有其他语言都支持更方便的处理JSON的方式。 - Bill Karwin

但这个我必须在Mysql中完成。 - sneha

目前MySQL在JSON处理方面的功能非常有限，因此建议使用其他语言，但是可以实现的第一个近似方法类似于db-fiddle，根据需要调整脚本。 - wchiquito

Answer 1

你可以使用 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;

你可能需要做的不仅仅是简单的“SELECT product”；-）

注意：MySQL JSON函数是在5.7.8中添加的，因此您需要首先检查您的MySQL版本。

- weblaunchuk

Answer 2

扩展这个答案，如果你想循环遍历json对象的键，可以通过以下方式实现：

declare _keys int default 0;
declare i int default 0;
declare _current_key varchar(50) default '';
select JSON_KEYS(product) into _keys;
while i < JSON_LENGTH(_keys) do
    select json_extract(json_unquote(_keys), concat('$[', i, ']')) into _current_key;
    set i = i + 1;
end while;

- Muhammad Awais