在处理json串时我们经常遇到json里的数组，我们访问json数组可以使用mySQL的JSON_EXTRACT(jsonstr,"$[0].key")函数得到key的值；但是由于JSON_EXTRACT的第二参数$[0]是字符串，在存储过程或函数中遍历json数据时很容易犯得错误是直接将变量整数i写为JSON_EXTRACT(jsonstr,"$[i].key")形式这时根本无得到key的值，因为JSON_EXTRACT认为i也是字符串而非变量，这时就需要使用如下的写法就能正确的完成遍历JSON_EXTRACT(jsonstr,CONCAT('$[',CONVERT(i,CHAR),'].key'))，这样我们无论是用FOR 或WHILE 都可以方便的遍历Json串中的多行数据。以下是一个遍历json数组的存储过程。

 WHILE i<m DO
          SET  ic=CONVERT(i,CHAR);#concat('$[',ic,'].probability')
           INSERT INTO `custom_data_1066_invoicedetails` 
                        SET dguid= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].guid')),'"',''),
                            invoicecodeconfirm= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].InvoiceCodeConfirm')),'"',''),
                            invoicenum= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].InvoiceNum')),'"',''),
                            InvoiceType= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].InvoiceType')),'"',''),
                            VatType= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].VatType')),'"',''),
                            InvoiceDate= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].InvoiceDate')),'"',''),
                            InvoiceTypeOrg= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].InvoiceTypeOrg')),'"',''),
                            AmountInFiguers= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].AmountInFiguers')),'"',''),
                            TotalTax= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].TotalTax')),'"',''), 
                            TotalAmount= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].TotalAmount')),'"',''),  
                            dCommodityName= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].CommodityName')),'"',''),
                            TaxRate= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].TaxRate')),'"',''),
                            probability= REPLACE(JSON_EXTRACT(JSON_EXTRACT(mdetails,"$.details"),CONCAT('$[',ic,'].probability')),'"',''),
                            created_at=CURRENT_TIMESTAMP(),
                            data_id=tmp;
                    SET i=i+1;
         END WHILE;