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的所有内容 |