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"}');
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"}');
-- =====================================================================================================
-- 查询操作 json字段名->’$.json属性’
select * from dept where json_value->'$.deptLeaderId' = '5'
-- 联表查询
SELECT
*
FROM
dept,
dept_leader
WHERE
dept.json_value -> '$.deptLeaderId' = dept_leader.json_value -> '$.id';
-- ->会保持json文档格式中原来格式,但->>会把所有引号去掉;->当做where查询是要注意类型的,->>是不用注意类型的
SELECT json_value -> '$.deptName' FROM dept
SELECT json_value ->> '$.deptName' FROM dept
-- 使用order by
SELECT * FROM dept ORDER BY json_value->'$.deptId' DESC
-- 多个属性查询或者单个属性查询也可以用JSON_EXTRACT()函数-json的提取函数
SELECT
*
FROM
dept
WHERE
JSON_EXTRACT( json_value, '$.deptName' ) = '部门3'
AND JSON_EXTRACT( json_value, '$.deptId' ) = '3'
-- JSON_CONTAINS()函数 JSON 文档是否在路径中包含特定对象;
SELECT
*
FROM
dept
WHERE
JSON_CONTAINS ( json_value -> '$.deptLeaderId', '"5"' );
-- JSON_OBJECT():将一个键值对列表转换成json对象
SELECT * FROM dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))
-- JSON_OBJECT():将一个键值对列表转换成json对象
insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');
SELECT * FROM dept
select *,json_value->'$.deptName' as deptName from dept
select deptName from (select *,json_value->'$.deptName' as deptName from dept) as a
where JSON_CONTAINS(deptName, JSON_OBJECT('depp','dd'))
-- JSON_ARRAY():创建JSON数组
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"}');
select * from dept
SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1","2"))
-- JSON_TYPE():查询某个json字段属性类型
select json_value->'$.deptName',JSON_TYPE(json_value->'$.deptName') as type from dept
-- JSON_KEYS():JSON文档中的键数组
SELECT JSON_KEYS(json_value) FROM dept
-- JSON_SET():将数据插入JSON格式中,有key则替换,无key则新增
update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;
-- 如果不带之前的值则会覆盖
update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2
UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2')
WHERE id=2
-- JSON_REPLACE()替换
UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;
-- JSON_REMOVE():从JSON文档中删除数据
UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;
参考链接学习:https://www.cnblogs.com/Bkxk/p/17384948.html