ZPY博客

SpringBoot操作Mongo使用aggregate关联查询时的条件顺序问题

前两天测试时发现SpringBoot使用Mongo的aggregate关联查询时分页查询出来数据的条数与直接取count的结果不一致,仔细分析代码后发现使用aggregate时条件的顺序是有影响的。

有问题的代码:

List<Bson> aggregateList = new ArrayList<>(1);

aggregateList.add(Aggregates.lookup(announceColName, "announceId", "_id", "announceData"));
aggregateList.add(Aggregates.sort(new BasicDBObject("announceCreateTime", -1)));
aggregateList.add(Aggregates.skip((pageNum - 1) * pageSize + plus));
aggregateList.add(Aggregates.limit(pageSize));
aggregateList.add(Aggregates.match(condition));

AggregateIterable<Document> documents = collection.aggregate(aggregateList);

正确的代码如下:

List<Bson> aggregateList = new ArrayList<>(1);

aggregateList.add(Aggregates.match(condition));
aggregateList.add(Aggregates.lookup(announceColName, "announceId", "_id", "announceData"));
aggregateList.add(Aggregates.sort(new BasicDBObject("announceCreateTime", -1)));
aggregateList.add(Aggregates.skip((pageNum - 1) * pageSize + plus));
aggregateList.add(Aggregates.limit(pageSize));

AggregateIterable<Document> documents = collection.aggregate(aggregateList);

可以看到,正确的代码是先match主表的where条件,再分页,而有问题的代码是先分页再match主表的where条件,所以才会出现查询结果不一致的问题。所以aggregate的顺序必须按照先where条件=>再排序=>再分页的顺序,不然就会出现问题。