关于mysql中的json解析函数JSON_EXTRACT
目录
- mysql json解析函数JSON_EXTRACT
- 例子
- mysql5.7 json格式与json_extract方法
- 数据初始化
- select语句
- 返回结果解析
- 用法解析
mysql json解析函数JSON_EXTRACT
MYSQl自带的解析函数JSON_EXTRACT,用JSON_EXTRACT函数解析出来的函数会包含双引号
例子
t_table表里面source字段是以json格式写的值为
{ "info" : { "color" : "白色", "inner_color" : "米色", "number" : "12345678", "registration_date" : "2012-11" }, "accessory" : [ "1", "4", "5", "6", "7", "8", "9", "10" ], "remark" : "测试"}
查询color不去掉双引号,inner_color去掉双引号,remark去掉双引
select source->'$.info.color' as color, replace(source->'$.info.inner_color','"','') as inner_color, replace(source->'$.remark','"','') as remark from t_table
查询结果
color | inner_color | remark |
---|---|---|
“白色” | 米色 | 测试 |
mysql5.7 json格式与json_extract方法
数据初始化
json_test表数据,id和jsonstr字段(类型json)
{ "no": "7", "title": "运动方式", "content": [{ "text": "您平时经常进行的运动及时间", "item1": [{ "text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected" }, { "text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected" }], "item2": [{ "text": "慢跑222走跑结合", "type": "text", "value": "慢跑2" }, { "text": "上下楼梯 / 爬山2", "type": "number", "value": "33" }] }] }
select语句
SELECT id, json_extract( t.jsonstr, '$.*' ), json_extract( t.jsonstr, '$.title' ) AS "title", json_extract( t.jsonstr, '$.content' ) AS "content" , json_extract( t.jsonstr, '$**.text' ) AS "text" , json_extract( t.jsonstr, '$.content[*].item1[*]' ) AS "item1" FROM json_test t;
返回结果解析
//json_extract( t.jsonstr, '$.*' )返回: ["7", "运动方式", [{"text": "您平时经常进行的运动及时间", "item1": [{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}], "item2": [{"text": "慢跑222走跑结合", "type": "text", "value": "慢跑2"}, {"text": "上下楼梯 / 爬山2", "type": "number", "value": "33"}]}]] //json_extract( t.jsonstr, '$.title' ) AS "title"返回: "运动方式" //json_extract( t.jsonstr, '$.content' ) AS "content" 返回: [{"text": "您平时经常进行的运动及时间", "item1": [{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}], "item2": [{"text": "慢跑222走跑结合", "type": "text", "value": "慢跑2"}, {"text": "上下楼梯 / 爬山2", "type": "number", "value": "33"}]}] //json_extract( t.jsonstr, '$**.text' ) AS "text" 返回: ["您平时经常进行的运动及时间", "慢跑 / 快走 / 走跑结合", "上下楼梯 / 爬山", "慢跑222走跑结合", "上下楼梯 / 爬山2"] //json_extract( t.jsonstr, '$.content[*].item1[*]' ) AS "item1" 返回: [{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}]
用法解析
‘$.*’ | 返回全部json |
‘$.title’ | 返回key=”title”的数据 |
‘$**.text’ | 返回所有最底层key=”text”的数据 |
‘$.content[*].item1[*]’ | 返回key=content的list的key=item1的list的所有内容 |
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json.html
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)