如何实现MyBatis-Plus中针对MySql JSON类型数据的查询操作？

本文共计1109个文字，预计阅读时间需要5分钟。

创建表结构及初始化数据：sqlCREATE TABLE json_test ( id INT NOT NULL AUTO_INCREMENT, roles JSON DEFAULT NULL COMMENT '角色', project JSON DEFAULT NULL COMMENT '项目', PRIMARY KEY (id)) ENGINE=InnoDB;

表结构和初始数据

• 新建表结构

CREATE TABLE `json_test` ( `id` int NOT NULL AUTO_INCREMENT, `roles` json DEFAULT NULL COMMENT '角色', `project` json DEFAULT NULL COMMENT '项目', PRIMARY KEY (`id`)) ENGINE=InnoDB;

• 初始数据

INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (1, '[{"id": 10001, "name": "管理员"}, {"id": 10002, "name": "开发"}, {"id": 10003, "name": "测试"}]', '{"id": 11111, "name": "项目1"}');INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (2, '[{"id": 10002, "name": "开发"}]', '{"id": 22222, "name": "项目2"}');INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (3, '[{"id": 10003, "name": "测试"}]', '{"id": 11111, "name": "项目1"}');

其中roles字段存的是数组

[{"id":10001,"name":"管理员"},{"id":10002,"name":"开发"},{"id":10003,"name":"测试"}]

project存的是对象

{"id": 11111, "name": "项目1"}

对象JSON查询

查询project是项目1的（精准查询）

• 使用箭头函数

SELECT * FROM json_test WHERE project -> '$.name' = '项目1'复制代码

• 使用JSON_CONTAINS

SELECT * FROM json_test WHERE JSON_CONTAINS(project, JSON_OBJECT('name', '项目1'))

模糊查询project字段

SELECT * FROM json_test WHERE project -> '$.name' like '%项目%'

数组JSON查询

查询roles中包含测试的（精准查询）

SELECT * FROM json_test WHERE JSON_CONTAINS(roles,JSON_OBJECT('name','测试'))

模糊查询roles字段

SELECT * FROM json_testWHERE roles -> '$[*].name' like '%测%'

其中，中括号里的星号，也可以替换为数组中的下标index

SELECT * FROM json_testWHERE roles -> '$[1].name' like '%测%'

这样就是模糊查询在数组中第二个元素是否含有测字的，

提取JSON中数据

提取json中单个字段的值

SELECT id, roles -> '$[*].name' AS roleName, project ->> '$.name' AS projectName FROM json_test

也提供了JSON_EXTRACT函数，结果和上面是一样的，只是查询出的projectName是带双引号的

SELECT id, JSON_EXTRACT( roles, '$[*].name' ) AS roleName, JSON_EXTRACT( project, '$.name' ) AS projectName FROM json_test

如果不需要双引号，可以使用JSON_UNQUOTE函数

提取json中所有字段的值，用星号就行

SELECT id, roles -> '$[*].*' AS roleName, project ->> '$.*' AS projectName FROM json_test

一个箭头和两个箭头的区别

MySql中箭头函数，一个箭头和两个箭头的区别

如果是字符串，一个箭头返回的结果是带双引号的（只针对字符串），两个箭头是不带的

SELECT project -> '$.name' as projectName FROM json_test;"项目1""项目2""项目1"SELECT project ->> '$.name' as projectName FROM json_test;项目1项目2项目1

JSON_UNQUOTE函数也是去掉双引号，等价于使用两个箭头

SELECT project ->> '$.name' as projectName FROM json_test等价于SELECT JSON_UNQUOTE(project -> '$.name') as projectName FROM json_test等价于SELECT JSON_UNQUOTE(JSON_EXTRACT(project, '$.name')) as projectName FROM json_test

可以看出还是箭头函数方便

复杂JSON

多层复杂的json结构，提取想要的数据，也只需要按照key点下去即可

{ "status": 1, "message": "SUCCESS", "data": { "list": [ { "id": "1565510388254765086", "name": "测试1111", "object": [ { "id": "1565510583118209025", "tenderName": "客户1" } ] }, { "id": "1565504356392558665", "name": "测试2222", "object": [ { "id": "1565504633934229506", "tenderName": "客户2" } ] } ] }}SELECT other ->> '$.data.list[*].contractObject' FROM json_test - other 为表字段名

如果不知道key，也可以用星号代替，但是也要知道是在第几层

SELECT other ->> '$**.contractObject' FROM json_test

在LambdaQueryChainWrapper中使用

在MyBatis-Plus中 使用LambdaQueryChainWrapper模糊查询json数据

new LambdaQueryChainWrapper<>(baseMapper) .like(StringUtils.isNotBlank(req.getTitle()), Test::getTitle, req.getTitle()) .apply(StringUtils.isNotBlank(req.getRoles()), "roles ->> '$[*].name' LIKE CONCAT('%',{0},'%')", req.getRoles()) .apply(StringUtils.isNotBlank(req.getProject()), "project -> '$.name' LIKE CONCAT('%',{0},'%')", req.getProject()) .eq(req.getDeleted() != null, Test::getDeleted, req.getDeleted()) .orderByDesc(Test::getCreatedAt) .list();

使用LambdaQueryChainWrapper准确查询json数据

new LambdaQueryChainWrapper<>(baseMapper) .apply(StringUtils.isNotBlank(req.getRoles()), "JSON_CONTAINS(roles, JSON_OBJECT('name',{0}))", req.getRoles()) .apply(StringUtils.isNotBlank(req.getProject()), "project -> '$.name' = {0}", req.getProject()) .orderByDesc(Test::getCreatedAt) .list();

使用LambdaQueryChainWrapper的话 ，一般都是用apply拼接自定义sql

apply是可以通过占位符的形式，传入多个参数的，也比较方便

LambdaQueryChainWrapper select 提取JSON数据

apply是用做数据筛选用，那如果想select提取json中的数据呢

• 首先需要在对应的实体类里新增一个字段，比如roleName（不需要在表中创建该字段）

@TableField(value = "roles ->> '$[*].name'", insertStrategy = FieldStrategy.NEVER, updateStrategy = FieldStrategy.NEVER, select = false)private String roleName;

其中，value对应的就是select xxxx形式

insertStrategy = FieldStrategy.NEVER不执行插入

updateStrategy = FieldStrategy.NEVER不执行更新

select = false不让该字段出现在select中

上述3个配置是为了不影响已有的增删改查，因为只是在实体类中新增了字段，没有在对应的表中增加该字段，可以根据自己的实际需求配置

• 然后就可以在LambdaQueryChainWrapper中使用了

new LambdaQueryChainWrapper<>(baseMapper) .select(Test::getRoleName) .list();

对应执行的sql如下

SELECT roles ->> '$[*].name' AS roleName FROM json_test

这种方法可以在select中用MySql里的函数，比如常用的SUM、MAX、COUNT等

@TableField(value = "SUM(money)", insertStrategy = FieldStrategy.NEVER, updateStrategy = FieldStrategy.NEVER, select = false)private BigDecimal moneyCount;

哎，就是不想在xml中写sql...(小声bb)