likes
comments
collection

MySql中json类型数据的查询以及在MyBatis-Plus中的使用

作者站长头像
站长
· 阅读数 32

我报名参加金石计划1期挑战——瓜分10万奖池,这是我的第1篇文章,点击查看活动详情

表结构和初始数据

  • 新建表结构
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"}');

MySql中json类型数据的查询以及在MyBatis-Plus中的使用

其中 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_test
WHERE roles -> '$[*].name' like '%测%'

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

SELECT * FROM json_test
WHERE 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

MySql中json类型数据的查询以及在MyBatis-Plus中的使用

如果不需要双引号,可以使用 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 是可以通过占位符的形式,传入多个参数的,也比较方便

MySql中json类型数据的查询以及在MyBatis-Plus中的使用

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)