MongoDB复杂查询示例和Expression的使用

Tags: /Java/ Date Created:

Spring Data MongoDb - Criteria equivalent to a given query that uses $expr - Stack Overflow

MongoDB数据库，有以下实体

Demo {
String id; // PK
Long eid;
Long recordId;
String recordStatus; // "A", "P", "V", "R", "T", "E"
String refNo;
}

可能存在refNo相同但是recordId和recordStatus不同的记录。

现在需要查询所有数据，根据 eid进行过滤，若 refNo 相同的情况下只返回 recordId 最大的记录。

然后对查询的数据进行过滤，例如返回所有 recordStatus 或者只返回给定 recordStatus（例如 "T"）的数据，并进行refNo倒序排序和分页处理，然后返回结果。例如返回下图中，打勾✔行的数据：

类似的SQL语句如下：

select A.*
from Demo A, (
select id, refNo, max(recordId) as recordId
from Demo
where eid=111111 and A.refNo = "abcd123456" -- other filters...
group by refNo
) B
where A.id = B.id and recordStatus in ("D", "R", "P", "V", "E", "T")
order by enrolmentRefNo desc
limit 0, 10

等价的 Mongodb查询是什么？如何用Java实现相关查询功能？

等价的MongoDB查询是:

db.getCollection('Demo').aggregate([
{
$match: {
eid: 12345678,
refNo: "ABCD123456" // other filters...
}
},
{
$group: {
_id: "$refNo",
maxRecordId: { $max: "$recordId" },
docs: { $push: "$$ROOT" }
}
},
{ $unwind: "$docs" },
{
$match: {
$expr: {
$and: [
{ $eq: ["$docs.recordId", "$maxRecordId"] },
{ $in: ["$docs.recordStatus", ["D", "R", "P", "V", "E", "T"]] }
]
}
}
},
{ $sort: { "docs.refNo": -1 } },
{ $skip: 0 },
{ $limit: 100 },
{ $replaceRoot: { newRoot: "$docs" } }
])

这个聚合管道执行了以下步骤：

匹配指定条件的文档。
使用 $group 阶段按照 refNo 分组，同时计算每个分组中 recordId 的最大值。
使用 $unwind 阶段展开分组后的文档数组。
再次进行匹配，选择具有最大 recordId 且 recordStatus 在指定范围内的文档。
使用 $sort 阶段按照 refNo 进行倒序排序。
使用 $skip 和 $limit 阶段进行分页。
最后，使用 $replaceRoot 阶段将文档结构恢复为原始的 Demo 文档结构。

转换成Java/Spring代码：

public Page<Demo> findPageList(Integer page, Integer size, Demo dto) {
Criteria matchCriteria = new Criteria();
if (StringUtils.isNotBlank(dto.getRefNo())) {
matchCriteria.and("refNo").is(dto.getRefNo());
}
if (!isEmpty(dto.geteid())) {
matchCriteria.and("eid").is(dto.geteid());
}
AggregationOperation matchOperation = Aggregation.match(matchCriteria);
AggregationOperation groupOperation = Aggregation.group("$refNo")
.max("recordId").as("maxRecordId")
.push("$$ROOT").as("docs");
AggregationOperation unwindOperation = Aggregation.unwind("docs");
// Parse recordStatus filter
List<String> recordStatus;
if (isEmpty(dto.getRecordStatus())) {
recordStatus = "['A', 'P', 'V', 'R', 'T', 'E']";
} else {
recordStatus = "['" + dto.getRecordStatus + "']";
}
AggregationExpression expression = AggregationExpression.from(MongoExpression.create(("$expr: {\n" +
"\t$and: [\n" +
"\t\t{ $eq: ['$docs.recordId', '$maxRecordId'] },\n" +
"\t\t{ $in: ['$docs.recordStatus', $PLACE] }\n" +
"\t]\n" +
"}").replace("$PLACE", recordStatus)));
AggregationOperation filterOperation = Aggregation.match(expression);
AggregationOperation sortOperation = Aggregation.sort(Sort.Direction.DESC, "docs.refNo");
AggregationOperation skipOperation = Aggregation.skip(page * size);
AggregationOperation limitOperation = Aggregation.limit(size);
AggregationOperation replaceRootOperation = Aggregation.replaceRoot("docs");
AggregationOperation countOperation = Aggregation.count().as("totalCount");
Aggregation count = Aggregation.newAggregation(
matchOperation, groupOperation, unwindOperation, filterOperation, countOperation
);
CountResult queryResult = mongoTemplate.aggregate(count, Demo.class, CountResult.class).getUniqueMappedResult();
long totalRecord = queryResult == null ? 0 : queryResult.getTotalCount();
if (totalRecord == 0) {
return new PageImpl<>(new ArrayList<>(0), PageRequest.of(page, size), 0);
}
Aggregation aggregation = Aggregation.newAggregation(
matchOperation,
groupOperation,
unwindOperation,
filterOperation,
sortOperation,
skipOperation,
limitOperation,
replaceRootOperation
);
List<Demo> pageResult = mongoTemplate.aggregate(aggregation, Demo.class, Demo.class).getMappedResults();
return new PageImpl<>(list, PageRequest.of(page, size), totalRecord);
}
@Data
private static class CountResult {
private long totalCount;
}