mysql怎么解析json数组

本篇内容主要讲解“mysql怎么解析JSON数组”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“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语句：

SELECTREPLACE (JSON_EXTRACT (mobile, '$.contactName'),'"','') AS 'contactName',REPLACE (JSON_EXTRACT (mobile, '$.contactMobile'),'"','') AS 'contactMobile',REPLACE (JSON_EXTRACT (mobile, '$.contactJobTitle'),'"','') AS 'contactJobTitle'FROMcscw_clientWHEREid = 'XXXXXXXXXXXXXXX'

到此，相信大家对“mysql怎么解析json数组”有了更深的了解，不妨来实际操作一番吧！这里是编程网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！