mysql5.7json使用

create table t1(jdoc json);
insert into t1 values(’
{
“hey”: “guy”,
“anumber”: 243,
“anobject”: {
“whoa”: “nuts”,
“anarray”: [
1,
2,
“three”
],
“more”: “stuff”
},
“awesome”: true,
“bogus”: false,
“meaning”: null,
“japanese”: “明日がある。”,
“link”: “http://jsonview.com“,
“notLink”: “http://jsonview.com is great”
}
‘);
mysql> select json_depth(jdoc) from t1; 返回json文本的深度
+——————+
| json_depth(jdoc) |
+——————+
| 4 |
+——————+
1 rows in set (0.02 sec)
mysql> select json_length(jdoc) from t1; 返回json文本的长度
+——————-+
| json_length(jdoc) |
+——————-+
| 9 |
+——————-+
1 rows in set (0.01 sec)
mysql> select json_type(jdoc) from t1; 返回json值得类型
+—————–+
| json_type(jdoc) |
+—————–+
| OBJECT |
+—————–+
1 rows in set (0.04 sec)
mysql> SELECT JSON_VALID(‘hello’), JSON_VALID(‘“hello”’); 判读是否是合法的json类型
+———————+———————–+
| JSON_VALID(‘hello’) | JSON_VALID(‘“hello”’) |
+———————+———————–+
| 0 | 1 |
+———————+———————–+
1 row in set (0.00 sec)
查看存储的json有哪些key
mysql> select json_keys(jdoc) from t1;
+———————————————————————————————-+
| json_keys(jdoc) |
+———————————————————————————————-+
| [“hey”, “link”, “bogus”, “anumber”, “awesome”, “meaning”, “notLink”, “anobject”, “japanese”] |
+———————————————————————————————-+
1 rows in set (0.05 sec)
查看anobject有哪些key
mysql> select json_keys(jdoc,’anobject’) from t1;
+——————————+
| json_keys(jdoc,’.anobject′)|+——————————+|[“more”,“whoa”,“anarray”]|+——————————+5rowsinset(0.00sec)查看第一次出现的位置mysql>selectjsonsearch(jdoc,′one′,′nuts′)fromt1;+——————————–+|jsonsearch(jdoc,′one′,′nuts′)|+——————————–+|“.anobject.whoa” |
+——————————–+
1 rows in set (0.04 sec)
查看所有包含json的位置
mysql> select json_search(jdoc,’all’,’%json%’) from t1;
+———————————-+
| json_search(jdoc,’all’,’%json%’) |
+———————————-+
| [“link”, “notLink”] |
+———————————-+
1rows in set (0.00 sec)
抽取值
mysql> select json_extract(jdoc,’link[0]’) from t1;
+——————————–+
| json_extract(jdoc,’link[0]’) |
+——————————–+
| “http://jsonview.com” |
+——————————–+
1 rows in set (0.00 sec)
mysql> select json_extract(jdoc,’anobject[0].more[0]’) from t1;
+——————————————–+
| json_extract(jdoc,’anobject[0].more[0]’) |
+——————————————–+
| “stuff” |
+——————————————–+
1 rows in set (0.00 sec)
或者使用下面的方式
mysql> select jdoc,jdoc->’key2’ from t1;
+———————————————————————–
{“key1”: “value1”, “key2”: “value2”} | “value2” |
追加记录
mysql> select JSON_ARRAY_APPEND(jdoc,’name’,’xxx’) from t1;
+—————————————————————————————–

| JSON_ARRAY_APPEND(jdoc,’name’,’xxx’)
|

| {“key1”: “value1”, “key2”: “value2”}
|
| {“age”: 28, “name”: [“baixiaoyu”, “xxx”]}
|
| {“age”: 28, “name”: [“baixiaoyu”, “xxx”]}
|
| {“age”: 28, “name”: [“baixiaoyu”, “xxx”]}
删除json数据
mysql> select JSON_REMOVE(jdoc,’name’) from t1;
+———————————————————————————————————————————————————————–
——————————————————————————————————+
| JSON_REMOVE(jdoc,’name’)
|
+———————————————————————————————————————————————————————–
——————————————————————————————————+
| {“key1”: “value1”, “key2”: “value2”}
|
| {“age”: 28}
|
| {“age”: 28}
|
| {“age”: 28}
|
有就替换，没有就insert
mysql> select JSON_set(jdoc,’name’,’ooo’) from t1;
+———————————————————————————————————————————————————————–
| JSON_set(jdoc,’$.name’,’ooo’)
|
+——————————————————————————————————————–
| {“key1”: “value1”, “key2”: “value2”, “name”: “ooo”}
|
| {“age”: 28, “name”: “ooo”}
|
| {“age”: 28, “name”: “ooo”}
|
| {“age”: 28, “name”: “ooo”}

查询结果去掉双引号
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(jdoc, ‘name’)) AS name from t1;
+———–+
| name |
+———–+
| NULL |
| baixiaoyu |
| baixiaoyu |
| baixiaoyu |
| NULL |
+———–+