黄东旭解析 TiDB 的核心优势
1577
2023-07-05
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://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://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'登录后复制
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。