mysql json remove_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