阅读数:238 评论数: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;

其中roles字段存的是数组

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

project存的是对象

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

二、mysql中关于json的使用

关于mysql中json的使用，参考：http://1024s.top/mbstudy/mbBlog/blogDetail?blogId=41824

1、对象json查询

（1）使用箭头函数

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

（2）使用json_contains

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

2、数组查询

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

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

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

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

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

3、提出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

4、复杂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 

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

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

三、在LambdaQueryChainWrapper中使用

1、模糊查询

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();

2、准确查询

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();

3、数据筛选

apply 是可以通过占位符的形式，传入多个参数的，也可用做数据筛选用。

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

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

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

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

（2）然后就可以在 LambdaQueryChainWrapper 中使用了

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

4、当 JSON 字段中的一个字段不存在时插入，存在时更新

import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

public class YourServiceImpl extends ServiceImpl<YourMapper, YourEntity> implements YourService {

    public void insertOrUpdateJsonField(Long id, String fieldName, String fieldValue) {
        YourEntity entity = new YourEntity();
        entity.setId(id);

        LambdaUpdateWrapper<YourEntity> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(YourEntity::getId, id)
                .setSql("your_json_column = JSON_SET(your_json_column, '$." + fieldName + "', '" + fieldValue + "')");

        baseMapper.insertOrUpdate(entity, updateWrapper);
    }
}