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

r囧r小猫 2024-03-30 15:52 66阅读 0赞

#### 表结构和初始数据 ####

*  新建表结构

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

![3a1dbcfb4615d5e2dd5e272c97a11e6e.png][]

其中 ***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

![6f63ef2eb9c301d00566ee25de2cd5e6.png][]

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

![086c0f9978b29da1f74c8fbf27ff8b8d.png][]

#### 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)

[3a1dbcfb4615d5e2dd5e272c97a11e6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e7a59bcd5571476b94dc8d128fd18cc4.png
[6f63ef2eb9c301d00566ee25de2cd5e6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/de22212b26c64e7591b5ea7255a7ad5b.png
[086c0f9978b29da1f74c8fbf27ff8b8d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c97212d2ee49495e87ce9d5e810a0448.png