本篇内容主要讲解“mysql怎么解析json数组”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql怎么解析json数组”吧!
mysql在5.7开始支持json解析了 也可以解析数组哦！
计算
安全
数据库
网络和加速
企业服务
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
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
字段名 ：mobile ,内容：{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}。
执行SQL：
查询结果：
结果带引号，并不能真正使用。
执行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'到此，相信大家对“mysql怎么解析json数组”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
