在处理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;