写在前面
需要注意,5.7以上版本才支持,但如果是生产环境需要使用的话,尽量使用8.0版本,因为8.0版本对json处理做了比较大的性能优化。你你可以使用
select version();
来查看版本信息。
本文看下MySQL的json处理。在正式开始让我们先来准备一些测试数据:
CREATE TABLE `dept` (
`id` int(11) NOT NULL,
`dept` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');
1:json字段名->’$.json属性’
通过json字段名->’$.json属性’
语法格式可以访问到json中某个key的值,以如下查询方式看下。
1.1:用在DQL查询结果中
mysql> select id,json_value->'$.deptLeaderId' from dept where id=2;
+----+------------------------------+
| id | json_value->'$.deptLeaderId' |
+----+------------------------------+
| 2 | "4" |
+----+------------------------------+
1 row in set (0.01 sec)
1.2:用在DQL条件中
- 单条件
mysql> select id from dept where json_value->'$.deptLeaderId'='4';
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.01 sec)
- 和普通字段组合查询
mysql> select id from dept where json_value->'$.deptLeaderId'='4' and id>1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.01 sec)
- json多条件
mysql> select id from dept where json_value->'$.deptLeaderId'='4' and json_value->'$.deptName' like '%部门%';
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
1.3:用在DQL关联查询中
先准备数据:
CREATE TABLE `dept_leader` (
`id` int(11) NOT NULL,
`leaderName` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');
- 关联查询
mysql> SELECT dept.id,dept_leader.id from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
+----+----+
| id | id |
+----+----+
| 1 | 3 |
| 2 | 4 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 6 | 5 |
| 7 | 5 |
| 8 | 5 |
+----+----+
8 rows in set (0.00 sec)
2:json_extract
语法格式json_extract(字段名,$.json字段名)
,用来从json字段中提取值,如下:
mysql> select id,json_extract(json_value,'$.deptName') as deptName from dept;
+----+------------------------------+
| id | deptName |
+----+------------------------------+
| 1 | "部门1" |
...
mysql> SELECT * FROM dept WHERE JSON_EXTRACT(json_value,'$.deptName') like '%部门%'\G
*************************** 1. row ***************************
id: 1
dept: 部门1
json_value: {"deptId": "1", "deptName": "部门1", "deptLeaderId": "3"}
*************************** 2. row ***************************
id: 2
dept: 部门2
json_value: {"deptId": "2", "newData": "新增的数据", "deptName": "新增的部门1111", "deptLeaderId": "4"}
...cx
3:JSON_OBJECT()
语法格式JSON_OBJECT(k,v[,k,v])
,用来转换指定数据为json object,如下:
mysql> select json_object("name", "张三", "age", 99);
+------------------------------------------+
| json_object("name", "张三", "age", 99) |
+------------------------------------------+
| {"age": 99, "name": "张三"} |
+------------------------------------------+
1 row in set (0.01 sec)
4:JSON_CONTAINS()
语法JSON_CONTAINS(target, candidate[, path])
,用来判断json格式中是否包含指定子对象,其中子对象需要通过json_object()生成,如下:
mysql> select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))
-> ;
+----+---------+-------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------+
| 5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"} |
+----+---------+-------------------------------------------------------------+
1 row in set (0.01 sec)
注意需要使用json_object转换为object。
添加如下数据,看一个嵌套json对象的例子:
insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');
判断嵌套对象是否存在:
mysql> SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_OBJECT("depp","dd"));
+----+---------+--------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+--------------------------------------------------------------------------------+
| 6 | 部门9 | {"deptId": "5", "deptName": {"depp": "dd", "dept": "de"}, "deptLeaderId": "5"} |
+----+---------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * from ( SELECT *,json_value->'$.deptName' as deptName FROM dept ) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));
...
5:JSON_ARRAY()
创建json数组,如下:
mysql> select json_array("pingpong","羽毛球","篮球");
+---------------------------------------------+
| json_array("pingpong","羽毛球","篮球") |
+---------------------------------------------+
| ["pingpong", "羽毛球", "篮球"] |
+---------------------------------------------+
mysql> select json_array(json_object("name", "张三"), "篮球", json_object("name", "李四"));
+------------------------------------------------------------------------------------+
| json_array(json_object("name", "张三"), "篮球", json_object("name", "李四")) |
+------------------------------------------------------------------------------------+
| [{"name": "张三"}, "篮球", {"name": "李四"}] |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
json格式化看下:
5.1:判断数组是否是否包含某子数组
准备数据:
insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');
mysql> SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"));
+----+---------+-------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------------+
| 7 | 部门9 | {"deptId": "5", "deptName": ["1", "2", "3"], "deptLeaderId": "5"} |
+----+---------+-------------------------------------------------------------------+
1 row in set (0.05 sec)
6:JSON_TYPE()
获取类型:
mysql> SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept;
+------------------------------+--------+
| json_value->'$.deptName' | type |
+------------------------------+--------+
| "部门1" | STRING |
| "新增的部门1111" | STRING |
| "部门3" | STRING |
| "部门4" | STRING |
| "部门5" | STRING |
| {"depp": "dd", "dept": "de"} | OBJECT |
| ["1", "2", "3"] | ARRAY |
| ["5", "6", "7"] | ARRAY |
+------------------------------+--------+
7:JSON_KEYS()
获取文档中所有的key,如下:
mysql> SELECT JSON_KEYS(json_value),json_value FROM dept where id=2\G
*************************** 1. row ***************************
JSON_KEYS(json_value): ["deptId", "newData", "deptName", "deptLeaderId"]
json_value: {"deptId": "2", "newData": "新增的数据", "deptName": "新增的部门1111", "deptLeaderId": "4"}
1 row in set (0.01 sec)
8:JSON_SET()
更新或者是添加kv,无责插入,有则更新,语法格式JSON_SET(json_doc, path, val[, path, val] …)
,如下:
- key存在时更新
mysql> select * from dept where id=1;
+----+---------+-------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "deptName": "部门1", "deptLeaderId": "3"} |
+----+---------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update dept set json_value=JSON_SET(json_value, '$.deptName', '部门1_更新后') where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=1;
+----+---------+-----------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-----------------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "deptName": "部门1_更新后", "deptLeaderId": "3"} |
+----+---------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
- key不存在时插入
mysql> select * from dept where id=1;
+----+---------+-----------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-----------------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "deptName": "部门1_更新后", "deptLeaderId": "3"} |
+----+---------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update dept set json_value=JSON_SET(json_value, '$.deptAddr', '赢创动力4楼') where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=1;
+----+---------+-------------------------------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "deptAddr": "赢创动力4楼", "deptName": "部门1_更新后", "deptLeaderId": "3"} |
+----+---------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 一个key不存在,一个key存在
mysql> select * from dept where id=1;
+----+---------+-------------------------------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "deptAddr": "赢创动力4楼", "deptName": "部门1_更新后", "deptLeaderId": "3"} |
+----+---------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update dept set json_value=JSON_SET(json_value, '$.deptAddr', '八维学院', '$.newKey', 'newKey的value') where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=1; +----+---------+-------------------------------------------------------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------------------------------------------------------------------------+
| 1 | 部门1 | {"deptId": "1", "newKey": "newKey的value", "deptAddr": "八维学院", "deptName": "部门1_更新后", "deptLeaderId": "3"} |
+----+---------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
9:JSON_INSERT()
语法格式JSON_INSERT(json_doc, path, val[, path, val] …)
,插入文档,当值已经存在时忽略,如下:
mysql> select * from dept where id=2;
+----+---------+-------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------+
| 2 | 部门2 | {"deptId": "2", "deptName": "部门2", "deptLeaderId": "4"} |
+----+---------+-------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> update dept set json_value=JSON_INSERT(json_value, '$.deptId', '已存在不更新', '$.addr', '西二旗') where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=2; +----+---------+----------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+----------------------------------------------------------------------------------+
| 2 | 部门2 | {"addr": "西二旗", "deptId": "2", "deptName": "部门2", "deptLeaderId": "4"} |
+----+---------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
10:JSON_REPLACE()
语法格式JSON_REPLACE(json_doc, path, val[, path, val] …)
,更新而不新增文档,如下:
mysql> update dept set json_value=JSON_REPLACE(json_value, '$.deptName', '部门2_新名称', '$.notExitsKey', '不会替换,因为不存在') where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=2; +----+---------+--------------------------------------------------------------------------------------------+
| id | dept | json_value |
+----+---------+--------------------------------------------------------------------------------------------+
| 2 | 部门2 | {"addr": "西二旗", "deptId": "2", "deptName": "部门2_新名称", "deptLeaderId": "4"} |
+----+---------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
11:JSON_REMOVE()
语法格式JSON_REMOVE(json_doc, path[, path] …)
,删除文档:
mysql> select * from dept where id=3;
+----+---------+-------------------------------------------------------------+
| id | dept | json_value |
+----+---------+-------------------------------------------------------------+
| 3 | 部门3 | {"deptId": "3", "deptName": "部门3", "deptLeaderId": "5"} |
+----+---------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update dept set json_value=JSON_REMOVE(json_value, '$.depeName', '$.deptLeaderId') where id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept where id=3;
+----+---------+----------------------------------------+
| id | dept | json_value |
+----+---------+----------------------------------------+
| 3 | 部门3 | {"deptId": "3", "deptName": "部门3"} |
+----+---------+----------------------------------------+
1 row in set (0.00 sec)
12:JSON_SEARCH
语法格式JSON_SEARCH(json_doc,one_or_all,search_str [,escape_char [,path] ...])
,
获取指定文档出现的位置,如果没有则返回NULL。
12.1:官方例子
- 准备数据
mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
Query OK, 0 rows affected (0.00 sec)
mysql> select @j;
+---------------------------------------------------------+
| @j |
+---------------------------------------------------------+
| ["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}] |
+---------------------------------------------------------+
1 row in set (0.01 sec)
- 查询
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc'); // 第二参数查询终止条件,设置为one,即只查询一个
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]" |
+-------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc'); // 第二个参数查询终止条件,设置为all,即查询所有匹配的
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"] |
+-------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi'); // 全局匹配不存在的,会返回NULL
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH(@j, 'all', '10'); // 全局匹配10,返回具体位置的具体key
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k" |
+------------------------------+
1 row in set (0.01 sec)
- 指定路径搜索
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k" |
+-----------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k" |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k" |
+-------------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------+
#指定搜索路径为数组中第二个元素内的第一个元素
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k" |
+-----------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x" |
+---------------------------------------------+
- 模糊匹配
mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"] |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"] |
+-------------------------------+
# 指定搜索路径,$[0] = "abc"
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]" |
+---------------------------------------------+
# $[2] = {"x":"abc"}
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x" |
+---------------------------------------------+
# $[1] = [{"k": "10"}, "def"] 模糊匹配无结果
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y" |
+-------------------------------------------+
12.2:实际例子
在日常开发过程中如果需要使用JSON_SEARCH查询的话,需要搭配IS NOT NULL来获取符合条件的数据,如果是返回是NULL则说明不存在,反之存在,所以,对于返回的具体位置信息我们一般是使用不到的,准备数据如下:
Create Table: CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL COMMENT '名字',
`age` int(4) unsigned NOT NULL COMMENT '年龄',
`info` text COMMENT '补充信息',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
INSERT INTO `user` (`name`, `age`, `info`) VALUES ('suhw', '23', '{"phone":"12312123434","language":["c++","java","go"]}');
INSERT INTO `user` (`name`, `age`, `info`) VALUES ('bob', '20', '{"phone":"18912123434","language":["c++","c","go","php"]}');
查询会java和go语言的数据:
mysql> select * from user where json_search(info, 'all', 'go') is not null and json_search(info, 'all', 'java') is not null;
+----+------+-----+--------------------------------------------------------+
| id | name | age | info |
+----+------+-----+--------------------------------------------------------+
| 1 | suhw | 23 | {"phone":"12312123434","language":["c++","java","go"]} |
+----+------+-----+--------------------------------------------------------+
1 row in set (0.00 sec)
写在后面
参考文章列表
【MySQL】对JSON数据操作(全网最全) 。
MySQL - json_search 小结 。