很多时候,我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。
1.对于mysql5.7以上版本
使用mysql的内置函数JSON_EXTRACT(column, '$.key'),这个函数有两个参数,第一个参数column代表json列的列名;第二个参数key代表json字符串中的某一个key。
SELECT JSON_EXTRACT('{"priceTag":"员工/合作关键人","priceDiscount":"90"}', '$.priceDiscount') AS '定价折扣';
对于简单的json字符串肯定是可以解析成功,但是对于嵌套数组的没试过。
2.对于mysql5.7以下版本
只能充分发挥已有函数的功能去截取实现,无论实现方式是存储过程还是简单的sql语句,其原理都是一样的。
第一步,将所有的花括号的闭括号'}'替换成英文逗号',';第二步,获取key的坐标keyIndex和长度keyLength;第三步,获取以key为起点,第一个英文逗号','的坐标symbolIndex;第四步,使用substring截取字符串SUBSTRING(targetJsonStr, keyIndex + keyLength, symbolIndex - keyIndex - keyLength);第五步,使用replace将双引号'"'替换成空字符串'',完工。
示例:从{"priceTag": "员工/合作关键人","priceDiscount": "90"}中获取priceDiscount的值。
SELECT REPLACE
(
SUBSTRING(
REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','),
LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','))+CHAR_LENGTH('priceDiscount":'),
LOCATE(
',',
REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',','),
LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',',')) + CHAR_LENGTH('priceDiscount":')
) - (LOCATE('priceDiscount":',REPLACE('{"priceTag":"员工/合作关键人","priceDiscount":"90"}','}',',')) + CHAR_LENGTH('priceDiscount":'))
),'"',''
) AS '定价折扣';