MYSQL中解析json格式数据

MYSQL中解析json格式数据

JSON_VALID(requestbody)

注：requestbody为指定字段

JSON_EXTRACT(requestbody, '$.body')

注：requestbody为指定字段，body为json中的key

JSON_LENGTH()

select * from  (select *,CASE     WHEN JSON_VALID(requestbody)     THEN JSON_LENGTH(JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(requestbody, '$.body'), '$.collectInfo'), '$.collectguideInfo') )     ELSE null     END as listSizefrom prptable where REQUESTTYPE = 'REGIST' and requestbody like '%docCode%' ) jsonStrwhere listSize > 1order by id desc 

来源地址：https://blog.csdn.net/billxin0621/article/details/130528416