mysql全面解析json/数组

mysql解析json数组

mysql在5.7开始支持json解析了 也可以解析数组哦!

直接上demo

 SELECT Substr(col, 2, Length(col) - 2), Length(col)
 FROM (SELECT Json_extract(Json_extract(Json_extract(state, "$.tpl"),"$.items"
 ), "$[0].url")
 AS col
 FROM page
 ORDER BY id DESC
 LIMIT 100) t;

JSON_EXTRACT可以解析sql , tpl就是你json的key值

如果是数组,用$[*].url  或者 $[0].url  获取全部的value 或者某个下标的url

下面这个demo可以直接复制到sql运行

 select JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT('{"tpl":{"items":[{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750","id":1542348252537},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750","id":1542348263477},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750","id":1542348269599},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750","id":1542348276124},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750","id":1542348282561},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750","id":1542348288150,"link":"http://www.baidu.com"}],"bottomItems":[],"title":"demo2","description":"","wxLogo":"","bodyStyleInline":{},"bg":"","bgType":"","bottomStyleInline":{},"bottomBg":"","bottomBgType":"","uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830","activeItemsName":"items","activeImgType":"","authInfo":{"role_list":[{"name":"test","access_key_list":[]},{"name":"审核人员","access_key_list":[]}],"city_list":[],"userId":3108779,"userName":"zhangyusheng","email":"zhangyusheng@xxx.com","mobile":"123123","trueName":"张昱升","isEmployee":true}}}', "$.tpl"), "$.items"), "$[0].url");

我们来分析一下

原始json为

{
 "tpl":{
 "items":[
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750",
 "id":1542348252537
 },
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750",
 "id":1542348263477
 },
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750",
 "id":1542348269599
 },
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750",
 "id":1542348276124
 },
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750",
 "id":1542348282561
 },
 {
 "type":"image",
 "config":{
 "expandable":true,
 "linkAble":true
 },
 "url":"https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750",
 "id":1542348288150,
 "link":"http://www.baidu.com"
 }
 ],
 "bottomItems":[
 
 ],
 "title":"demo2",
 "description":"",
 "wxLogo":"",
 "bodyStyleInline":{
 
 },
 "bg":"",
 "bgType":"",
 "bottomStyleInline":{
 
 },
 "bottomBg":"",
 "bottomBgType":"",
 "uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830",
 "activeItemsName":"items",
 "activeImgType":"",
 "authInfo":{
 "role_list":[
 {
 "name":"test",
 "access_key_list":[
 
 ]
 },
 {
 "name":"审核人员",
 "access_key_list":[
 
 ]
 }
 ],
 "city_list":[
 
 ],
 "userId":3108779,
 "userName":"zhangyusheng",
 "email":"zhangyusheng@xxx.com",
 "mobile":"23123",
 "trueName":"张昱升",
 "isEmployee":true
 }
 }
}
  • $.tpl就是获取tpl这个键key
  • $[0].url就是获取[{url:1},{url:2}] 这个数组第一个对象的url值 也就是1

mysql json字符串解析成对应字段

字段名 :mobile ,内容:{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}。

解决方法:JSON_EXTRACT

执行SQL:

查询结果:

结果带引号,并不能真正使用。

解决方法:REPLACE

执行SQL:

查询结果:

问题解决。

sql语句:

SELECT
REPLACE (
JSON_EXTRACT (mobile, '$.contactName'),
'"',
''
) AS 'contactName',
REPLACE (
JSON_EXTRACT (mobile, '$.contactMobile'),
'"',
''
) AS 'contactMobile',
REPLACE (
JSON_EXTRACT (mobile, '$.contactJobTitle'),
'"',
''
) AS 'contactJobTitle'
FROM
cscw_client
WHERE
id = 'XXXXXXXXXXXXXXX'
作者:ghostyusheng原文地址:https://blog.csdn.net/ghostyusheng/article/details/84260831

%s 个评论

要回复文章请先登录注册