68.MySQL的JSON格式支持

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 创建表
CREATE TABLE `testjsontable` (
  `id` int NOT NULL,
  `jsonfield` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建测试数据
insert into testjsontable (id, jsonfield) values (1,'{"email":"email","address":[{"line":"abc"},{"name":"456"}]}');
insert into testjsontable (id, jsonfield) values (2,'{"email":"email2","address":[{"line":"abc1"},{"name":"456"}]}');
insert into testjsontable (id, jsonfield) values (3,'{"email":"email","address":[{"line":"abc"},{"name":"123"}]}');

1
2
3
4
5
6
7

-- select * from testjsontable  

-- 查询json对象的值 
-- 一般使用JSON_EXTRACT 函数,不使用 ->> 符号,
select * from testjsontable where jsonfield ->> '$.email'='email';
-- 查询json对象的值
select * from testjsontable where JSON_EXTRACT(jsonfield,'$.email') ='email';

1
2
3
4
5
6

-- 1.先提取 config JSON 字段中 address 属性,得到数组
SELECT JSON_EXTRACT(jsonfield,'$.address') address FROM testjsontable;
-- 2. 再从 address 数组中查找 name 的值是否等于查找的值,返回 1 或 0,表示是否已找到
SELECT JSON_CONTAINS(JSON_EXTRACT(jsonfield,'$.address'), JSON_OBJECT('name', '456')) 是否已找到 FROM testjsontable;
-- 3.最终语句
select * from testjsontable where JSON_CONTAINS(JSON_EXTRACT(jsonfield,'$.address'), JSON_OBJECT('name', '456')) > 0;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- select * from testjsontable where id=2;

-- 1.JSON_SET() 替换现有值并添加不存在的值.如果大key不存在,JSON_SET无法操作大key的属性,例如 "$.newField.new1","new1" ,如果newField不存在,则无法成功,也不报错,需要使用JSON_MERGE_PATCH函数变通实现.
update testjsontable set jsonfield=JSON_SET(jsonfield,"$.email", "email3","$.newField","new") where id=2;
update testjsontable set jsonfield=JSON_SET(jsonfield,"$.address[0].line", "abc2") where id=2;

-- 2.JSON_INSERT() 只插入新值,但不替换现有值.在这种情况下, $.email 不会被更新,只会添加一个新的字段 $.newemail
update testjsontable set jsonfield=JSON_INSERT(jsonfield,"$.email", "email8","$.newemail","newemail") where id=2;

-- 3.JSON_REPLACE() 只替换现有值,在这种情况下, $.newreplace 不会被添加,只有 $.email 会被更新
update testjsontable set jsonfield=JSON_REPLACE(jsonfield,"$.email", "email9","$.newreplace","newreplace") where id=2;

-- 4.JSON_REMOVE() 从JSON文档中删除数据
update testjsontable set jsonfield=JSON_REMOVE(jsonfield,"$.email") where id=2;

-- 5.JSON_MERGE_PATCH() 合并两个JSON参数,使用后面的值覆盖前面的值,key相同就覆盖,不存在就添加.
update testjsontable set jsonfield=JSON_MERGE_PATCH(jsonfield,'{"email":"9","":""}') where id=2;

-- 6.JSON_CONTAINS_PATH() 查询指定Key是否存在,存在返回1,不存在返回0
select * from testjsontable where  JSON_CONTAINS_PATH(jsonfield,'one','$."email"')>0;