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

创建过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `json_example1`(json_object JSON)
  BEGIN

        DECLARE products json;
        DECLARE i INT DEFAULT 0;
        DECLARE categoryName VARCHAR(255) DEFAULT 0;
        /*assign json_object's value to products mysql json object*/
        SELECT json_object->"$.product" INTO products;

        loop1 : WHILE i < JSON_LENGTH(products) DO
                /*get categoryName from current products object in while loop */
                SET categoryName = JSON_EXTRACT(products,CONCAT('$[',i,'].categoryName'));
                /* insert categoryName into table */
                INSERT INTO product VALUES(NULL,SUBSTRING(categoryName,2,(LENGTH(categoryName)-2)));
                SET i = i + 1;
        END WHILE loop1;
    END

呼叫程序

mysql> call json_example1('{"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> select * from product;
+------------+---------------+
| product_id | product_name  |
+------------+---------------+
|          1 | Parker Pen    |
|          2 | Drawing Books |
+------------+---------------+
2 rows in set (0.03 sec)

额外的-
创建表 product ( product_id int（11）非空自动增量， product_name varchar（255）默认为空，主键( product_id )）engine=innodb auto\u increment=3默认字符集=1；