mysql JSON

-- 增
-- 1.直接新增json字符串
mysql> insert into `user`(name,info,status) values("小红",'[{"name": "小明", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}]',0);
mysql> SELECT * from `user`;
+----+------+-----------------------------------------------------------------------------------------------+--------+
| id | name | info                                                                                          | status |
+----+------+-----------------------------------------------------------------------------------------------+--------+
|  4 | 小红 | [{"name": "小明", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}] |      0 |
+----+------+-----------------------------------------------------------------------------------------------+--------+
-- 2.通过JSON_ARRAY(),JSON_OBJECT()等方法构造json
INSERT INTO `user` ( name, info, status )
VALUES(
		"小明",
	JSON_ARRAY(
	JSON_OBJECT( "name", "小红", "score", 58 ),
	JSON_OBJECT( "name", "小胖", "score", 68 ),
	JSON_OBJECT( "name", "小绿", "score", 78 )
	),0
);
mysql> SELECT * from `user`;
+----+------+-----------------------------------------------------------------------------------------------+--------+
| id | name | info                                                                                          | status |
+----+------+-----------------------------------------------------------------------------------------------+--------+
|  4 | 小红 | [{"name": "小明", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}] |      0 |
|  5 | 小明 | [{"name": "小红", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}] |      0 |
+----+------+-----------------------------------------------------------------------------------------------+--------+
-- 查
-- 通过JSON_EXTRACT()方法获得json字段的某个key的值,$代表此json,
mysql> select JSON_EXTRACT(info,'$[0].score') from `user`;
+---------------------------------+
| JSON_EXTRACT(info,'$[0].score') |
+---------------------------------+
| 58                              |
| 58                              |
+---------------------------------+
-- 可以简写
mysql> select info->'$[0].score' from `user`;
+--------------------+
| info->'$[0].score' |
+--------------------+
| 58                 |
| 58                 |
+--------------------+
-- 查询json对象的所有key
mysql> select JSON_KEYS(info->'$[0]') from `user`;
+-------------------------+
| json_keys(info->'$[0]') |
+-------------------------+
| ["name", "score"]       |
| ["name", "score"]       |
+-------------------------+
-- 修改
mysql> update user set info = JSON_SET(info,'$[0].score',99) where id = 5;
mysql> select name , info -> '$**.score' from `user`;
+------+---------------------+
| name | info -> '$**.score' |
+------+---------------------+
| 小明 | [58, 68, 78]        |
| 小红 | [99, 68, 78]        |
+------+---------------------+
-- 删除
mysql> update `user` set info = json_remove(info,'$[0].score') where id = 5;
mysql> select * from `user`;
+----+------+-----------------------------------------------------------------------------------------------+--------+
| id | name | info                                                                                          | status |
+----+------+-----------------------------------------------------------------------------------------------+--------+
|  4 | 小明 | [{"name": "小红", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}]   |    0   |
|  5 | 小红 | [{"name": "小明"}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}]                |    0   |
+----+------+-----------------------------------------------------------------------------------------------+--------+
-- 添加
mysql> update `user` set info = json_set(info,'$[0].score',88) where id = 5;
mysql> select * from `user`;
+----+------+-----------------------------------------------------------------------------------------------+--------+
| id | name | info                                                                                          | status |
+----+------+-----------------------------------------------------------------------------------------------+--------+
|  4 | 小明 | [{"name": "小红", "score": 58}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}]   |    0   |
|  5 | 小红 | [{"name": "小明", "score": 88}, {"name": "小胖", "score": 68}, {"name": "小绿", "score": 78}]   |    0   |
+----+------+-----------------------------------------------------------------------------------------------+--------+

• JSON_ARRAY() : 将差数转换成数组形式的json,可变参,返回json字符串

• JSON_OBJECT() : 将参数转换成对象(map)形式的json,可变参,但要是偶数,即参数成对存在,arg1为key,arg2为value,arg3为key,arg4为value

• JSON_EXTRACT() : 获得指定key的值,传入两个参数,第一个参数为json字符串,第二个参数指定key,$表示当前json,可用$[index]来获取指定下标的值,$[]中有三种写法

  1. $[index] : 直接获取index下标的值
  2. $[0 to 1] : 获取下标0到1的值,得到数组json,相当于subLsit操作
  3. $[*] : 获取所有
  • 可以通过**$.key\ast \ast 来获取$第一级中指定key的值,也可以通过**$.key**来获取所有子级key为key的值

• JSON_SET() : 修改json字符串中的指定值,第一个参数为json,第二个参数为表达式,第三个参数为修改后的值,可以如果key不存在即添加

• JSON_KEYS() : 查询json对象的所有key

• JSON_QUOTE() : 将json转成json字符串类型

• JSON_UNQUOTE() : 去除json字符串的引号,将值转换成varchar类型

• JSON_CONTAINS() : 判断是否包含某个json值

• JSON_SEARCH() : 按给定字符串关键字搜索json,返回匹配的路径

• JSON_MERGE() : 合并json数组或对象

• JSON_DPTH() : 返回json文档的最大深度

• JSON_LENGTH() : 返回json文档的长度

• JSON_TYPE() : 返回json值的类型

• JSON_VALID() : 判断是否为合法json文档