mysql中json_remove函数的使用?

需求描述:

　　今天看json记录,可以通过json_remove函数对一个key或多个key从个json记录中去掉.

操作过程:

1.查看一个已经存在的json表

mysql> select * from tab_json;
+----+---------------------------------------------------------------------------------------+
| id | data                                                                                  |
+----+---------------------------------------------------------------------------------------+
|  1 | {"age": 54, "tel": 15046464563, "passcode": "654567"}                                 |
|  2 | {"age": "33", "tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+----+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2.通过json_remove函数,将json记录中的key去掉

mysql> select json_remove(data,'$.age') from tab_json;  #查询时,去掉某些的key
+--------------------------------------------------------------------------+
| json_remove(data,'$.age')                                                |
+--------------------------------------------------------------------------+
| {"tel": 15046464563, "passcode": "654567"}                               |
| {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select json_remove(data,'$.age','$.name') from tab_json; #查询时,去掉某些的key
+---------------------------------------------------------+
| json_remove(data,'$.age','$.name')                      |
+---------------------------------------------------------+
| {"tel": 15046464563, "passcode": "654567"}              |
| {"tel": 189776542, "olds": "12", "address": "Hangzhou"} |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

通过update将json记录中的值进行替换

mysql> update tab_json set data = json_remove(data,'$.age'); #通过update已经将json记录进行了修改
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from tab_json;
+----+--------------------------------------------------------------------------+
| id | data                                                                     |
+----+--------------------------------------------------------------------------+
|  1 | {"tel": 15046464563, "passcode": "654567"}                               |
|  2 | {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+----+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

备注:json_remove函数的作用就是查询时,将其中的某些key不显示,如果要更新源表,需要使用update操作.

 

文档创建时间:2018年6月6日10:08:09