数据库其中一条数据格式如下:
{
"_id": "6220a1942fefba0066659",
"formDataId": "d885b3094c24160b3756997d59fe5175",
"formStructId": "cfed43eb74bab08ca36eb8343319d98e",
"qrCodeId": "2a445726c47ed0b344a0546abf09ac00",
"data": {
"list": [
{
"valueLabel": "88",
"name": "姓名",
"icon": "iconfont icon-a-xingmingxiao",
"options": {
"clearable": false,
"maxlength": "50",
"defaultValue": "",
"placeholder": "请输入姓名",
"required": false
},
"screen": false,
"type": "input",
"labelName": "姓名",
"title": "",
"value": "",
"tips": "请如实填写",
"key": "5b87fc3c-660c-455f-bdd0-0be475498fcd",
"defaultType": NumberInt("0")
},
{
"valueLabel": "男",
"name": "性别",
"icon": "iconfont icon-a-xingbiexiao",
"options": {
"defaultValue": "",
"block": true,
"required": false,
"valueData": [
{
"label": "男",
"key": NumberInt("1")
},
{
"label": "女",
"key": NumberInt("0")
}
]
},
"screen": false,
"type": "radio",
"labelName": "性别",
"title": "",
"value": "03",
"tips": "",
"key": "d01bf58b-436c-41b4-b869-fba60a354c65",
"defaultType": NumberInt("0")
},
{
"valueLabel": "1788888888",
"name": "手机号",
"icon": "iconfont icon-shoujihao",
"options": {
"clearable": false,
"maxlength": "11",
"defaultValue": "",
"placeholder": "请输入手机号",
"required": false
},
"screen": false,
"type": "input",
"labelName": "手机号",
"title": "",
"value": "",
"tips": "",
"key": "d21d1708-efba-468b-8ce6-16f0f243ed41",
"defaultType": NumberInt("0")
},
{
"valueLabel": "",
"name": "邮箱",
"icon": "iconfont icon-youxiang",
"options": {
"clearable": false,
"maxlength": "50",
"defaultValue": "",
"placeholder": "请输入邮箱",
"required": false
},
"screen": false,
"type": "input",
"labelName": "邮箱",
"title": "",
"value": "",
"tips": "",
"key": "95d36d9a-4992-4f84-97dc-c3637b4f87ab",
"defaultType": NumberInt("0")
}
]
},
"createBy": NumberLong("2138991"),
"createAt": ISODate("2022-03-03T09:04:32.619Z"),
"updateBy": NumberLong("2138991"),
"updateAt": ISODate("2022-03-03T09:04:32.619Z"),
"deleted": NumberInt("0"),
"_class": "net.trueland.tcloud.qrcode.entity.FormDataMongoEntity"
}
需求:根据data字段中list集合中 name = "姓名",valueLabel="88"模糊查询 两个条件在list下的同一个元素对象中必须同时满足,查询数据库中符合条件的数据
SQL:
db.T_Cloud_Form_Data.find({"data.list":{$elemMatch:{"name":"姓名","value":{ $regex:'88'}}}})
代码:
Query query = new Query();
Criteria criteria = new Criteria();
criteria.and("formStructId").is(req.getFormStructId());
// 筛选条件
if (StringUtils.isNotBlank(screeningName) && StringUtils.isNotBlank(filterContent)) {
filterContent = EscapeUtil.escapeExprSpecialWord(filterContent);
String pattern = ".*" + filterContent + ".*";
Criteria cri = Criteria.where("name").is(screeningName).and("value").regex(pattern);
criteria.and("data.list").elemMatch(cri);
log.info("条件criteria:{}",criteria);
}
criteria.and("deleted").ne(1);
query.addCriteria(criteria);
query.with(PageRequest.of(req.getPageNum() - 1, req.getPageSize(), Sort.by(Sort.Order.desc("createAt"))));
List<FormDataMongoEntity> data = mongoTemplate.find(query, FormDataMongoEntity.class, "T_Cloud_Form_Data");
需求二:根据data字段中list集合中 name = "姓名",valueLabel="88"模糊查询 两个条件在list数组对象中(此种情况不要求在数组下同一个元素对象中)同时满足,查询数据库中符合条件的数据
SQL:
db.T_Cloud_Form_Data.find({"formStructId":"35a551482d374ac4cfd2cbb9ae219208","data.list.name":"姓名","data.list.value":{ $regex:'88'}})
代码:
Query query = new Query();
Criteria criteria = new Criteria();
criteria.and("formStructId").is(req.getFormStructId());
// 筛选条件
if (StringUtils.isNotBlank(screeningName) && StringUtils.isNotBlank(filterContent)) {
criteria.and("data.list.name").is(screeningName);
filterContent = EscapeUtil.escapeExprSpecialWord(filterContent);
String pattern = ".*" + filterContent + ".*";
criteria.and("data.list.value").regex(pattern); // 模糊查询
}
criteria.and("deleted").ne(1);
query.addCriteria(criteria);
query.with(PageRequest.of(req.getPageNum() - 1, req.getPageSize(), Sort.by(Sort.Order.desc("createAt"))));
List<FormDataMongoEntity> data = mongoTemplate.find(query, FormDataMongoEntity.class, "T_Cloud_Form_Data");
long count = mongoTemplate.count(query, FormDataMongoEntity.class, "T_Cloud_Form_Data");