mysql解析json字符串相关问题
很多时候，我们需要在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和长度keyLengt
h；第三步，获取以key为起点，第⼀个英⽂逗号','的坐标symbolIndex；第四步，使⽤substring截取字符串SUBSTRING(targetJsonStr, keyIndex + keyLength, symbolIndex - keyIndex - keyLength)；第五步，使⽤replace将双引号'"'替换成空字符串''，完⼯。
⽰例：从{"priceTag": "员⼯/合作关键⼈","priceDiscount": "90"}中获取priceDiscount的值。
SELECT
REPLACE(
-- SUBSTRING(s，n，len)
-- 带有 len 参数的格式，从字符串 s 返回⼀个长度同 len 字符相同的⼦字符串，起始于位置 n。
SUBSTRING(
REPLACE(
'{"priceTag":"员⼯/合作关键⼈","priceDiscount":"90"}' ,
'}' ,
','
) , -- s 将初始字段中的有括号替换成','号
        -- LOCATE(substr,str)
-- 返回字符串substr在字符串str中第⼀次出现的位置从1开始计数。
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员⼯/合作关键⼈","priceDiscount":"90"}' ,
'}' ,
','
)
)
+ CHAR_LENGTH('priceDiscount":') ,-- n 起始位置
LOCATE(
',' ,
REPLACE(
'{"priceTag":"员⼯/合作关键⼈","priceDiscount":"90"}' ,
'}' ,
','
) ,
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员⼯/合作关键⼈","priceDiscount":"90"}' ,
'}' ,
','
)
) + CHAR_LENGTH('priceDiscount":') -- n后的第⼀个','号在 s 中所在的位置
) -(
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员⼯/合作关键⼈","priceDiscount":"90"}' ,
'}' ,
','
)
) + CHAR_LENGTH('priceDiscount":')
) -- 计算出了key值对应的value值的长度
) ,
'"' ,
''
) AS '定价折扣';　
⽂字描述：
1.先将原始字符串的右'}' 替换成 ','
mysql 字符串转数组
2.计算出key值在步骤1中的位置，当做字段截取的起始位置
3.计算出key值右边最近的⼀个','号在步骤1中的位置  - 步骤2的位置 = key值对应的value值即字段截取长度
4.截取value值两边的双引号，即得出value值
参考：
相关函数介绍
LOCATE函数
语法⼀：
LOCATE(substr,str)
返回字符串substr在字符串str中第⼀次出现的位置从1开始计数。
如：
SELECT LOCATE("a","abca")
查询结果：
语法⼆：
LOCATE(substr,str,pos)
返回字符串substr从pos往后数在字符串str中第⼀次出现的位置从1开始计数。
如：
SELECT LOCATE("a","abca",2) 
查询结果：
注：如果str、substr中任意⼀个字段为null则查询结果为null
  如果substr在str中不存在则返回0
SUBSTRING函数
MYSQL中获取⼦串函数 SUBSTRING(s，n，len) 带有 len 参数的格式，从字符串 s 返回⼀个长度同 len 字符相同的⼦字符串，起始于位置n。
也可能对 n 使⽤⼀个负值。假若这样，则⼦字符串的位置起始于字符串结尾的第 n 个字符，即倒数第 n 个字符，⽽不是字符串的开头位置。参考：
CHAR_LENGTH函数
返回函数内字符串的长度
如：
CHAR_LENGTH('priceDiscount":')
查询结果：　
踩坑背景：公司使⽤了MongoDB数据库存放数据，但是我所做的通⽤统计服务数据⼜存放在mysql内，
所以需要MongoDB的数据往mysql导⼊，如果是简单的数据倒是没啥，但是这个json数据就有点难搞了。
⽐如上⾯的例⼦：他的key值是固定的，顺序可能也⼀致。MongoDB⾥⾯的数据就不⼀样了
举⼏个例⼦：
{"lightspot":"问题解决效率⾼","scotoma":"1.未查客户逾期情况2.未核实客户⾝份"}
{"scotoma":"未咨询汇款⼈"}
{"lightspot":"核实客户⾝份；问题处理快"}
{"scotoma":"后台操作不应说出（我看下是哪个客户）","lightspot":"1.规范⽤语2.问题解决思路清晰"}
顺序不⼀致，key值不⼀定全
SELECT
REPLACE(
substr(
REPLACE(ifnull(`b`.`comment`, ''),'}', ','), -- 原始字段
locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ','))+ char_length('scotoma":'), -- 起始位置
IF(locate('scotoma":',
REPLACE(ifnull(`b`.`comment`, ''),
'}',
',')
) <> 0, -- 判定key值是否存在
locate(',',
REPLACE(ifnull(`b`.`comment`, ''), '}', ','),
locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ','))
+ char_length('scotoma":')
)
- (locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ',')) + char_length('scotoma":')) -- 有就正常取值获取取值长度
, 0) -- 没有就取长度0
), -- 获取value内容
'"',
''
)
FROM ctr_i_quality_judgement b
注：如果要运⾏我的这段sql只需要将sql中的`b`.`comment`替换成我上⾯举例的四个字符串中的任意⼀个即可
⽂字描述：
1.先将原始字符串的右'}' 替换成 ','
2.计算出key值在步骤1中的位置，当做字段截取的起始位置
3.判定key值在这个json中是否存在，存在则按照之前的计算逻辑获取value值的⼤⼩，不存在则取0
4.截取value值两边的双引号，即得出value值

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。