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"}');

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