In spring data mongodb how to achieve pagination for aggregation

Question

In spring data mongodb using mongotemplate or mongorepository, how to achieve pagination for aggregateion

Possible duplicate of [Efficient pagination of MongoDB aggregation?](http://stackoverflow.com/questions/21192929/efficient-pagination-of-mongodb-aggregation) — Matthew Leffler, Dec 23 '15 at 01:50

score 16 · Accepted Answer · edited May 23 '17 at 11:47

This is an answer to an old post, but I'll provide an answer in case anyone else comes along while searching for something like this.

Building on the previous solution by Fırat KÜÇÜK, giving the results.size() as the value for the "total" field in the PageImpl constructor will not making paging work the way, well, you expect paging to work. It sets the total size to the page size every time, so instead, you need to find out the actual total number of results that your query would return:

public Page<UserListItemView> list(final Pageable pageable) {
    long total = getCount(<your property name>, <your property value>);

    final Aggregation agg = newAggregation(
        skip(pageable.getPageNumber() * pageable.getPageSize()),
        limit(pageable.getPageSize())
    );

    final List<UserListItemView> results = mongoTemplate
        .aggregate(agg, User.class, UserListItemView.class)
        .getMappedResults();

    return new PageImpl<>(results, pageable, total);
}

Now, then, the best way to get the total number of results is another question, and it is one that I am currently trying to figure out. The method that I tried (and it worked) was to almost run the same aggregation twice, (once to get the total count, and again to get the actual results for paging) but using only the MatchOperation followed by a GroupOperation to get the count:

private long getCount(String propertyName, String propertyValue) {
    MatchOperation matchOperation = match(Criteria.where(propertyName).is(propertyValue));
    GroupOperation groupOperation = group(propertyName).count().as("count");
    Aggregation aggregation = newAggregation(matchOperation, groupOperation);
    return mongoTemplate.aggregate(aggregation, Foo.class, NumberOfResults.class).getMappedResults().get(0).getCount();
}

private class NumberOfResults {
    private int count;

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }
}

It seems kind of inefficient to run nearly the same query twice, but if you are going to page results, the pageable object must know the total number of results if you really want it to behave like paging. If anyone can improve on my method to get the total count of results, that would be awesome!

Edit: This will also provide the count, and it is simpler because you do not need a wrapper object to hold the result, so you can replace the entire previous code block with this one:

private long getCount(String propertyName, String propertyValue) {
    Query countQuery = new Query(Criteria.where(propertyName).is(propertyValue));
    return mongoTemplate.count(countQuery, Foo.class);
}

Try with PageableExecutionUtils where you don't have to count additionally — Arka Bandyopadhyay, Apr 09 '20 at 14:18
@ArkaBandyopadhyay The PageableExecutionUtils has optimization to skip the count in certain cases, but not always. It also inefficient as it assumes that you control list of the items and do not query for 1000000 items and then apply page of 20. nyxc below has proper solution. — vbg, Nov 05 '21 at 17:13

score 8 · Answer 2 · edited May 23 '17 at 12:34

8

In addition to ssouris solution you can use Pageable classes for the results.

public Page<UserListItemView> list(final Pageable pageable) {

    final Aggregation agg = newAggregation(
        skip(pageable.getPageNumber() * pageable.getPageSize()),
        limit(pageable.getPageSize())
    );

    final List<UserListItemView> results = mongoTemplate
        .aggregate(agg, User.class, UserListItemView.class)
        .getMappedResults();

    return new PageImpl<>(results, pageable, results.size())
}

edited May 23 '17 at 12:34

Community

1
1

answered May 16 '17 at 10:29

Fırat Küçük

5,613
2
50
53

1

pagination should be done on the mongodb server side to avoid massive data transfering – Will May 18 '17 at 22:30
@Will Aggregation framework fetches only the required data. Then we put the data into a pagination class. https://docs.mongodb.com/manual/reference/operator/aggregation/skip/ and https://docs.mongodb.com/manual/reference/operator/aggregation/limit/ – Fırat Küçük May 19 '17 at 07:50
5

Setting the total to `results.size()` in `new PageImpl<>()` is wrong IMO. You probably need to execute another query to get the total results. – nyxz Jun 16 '20 at 17:15

score 6 · Answer 3 · answered Dec 23 '15 at 14:53

You can use MongoTemplate

org.spring.framework.data.mongodb.core.aggregation.Aggregation#skip
        and 
org.springframework.data.mongodb.core.aggregation.Aggregation#limit

Aggregation agg = newAggregation(
        project("tags"),
        skip(10),
        limit(10)
);

AggregationResults<TagCount> results = mongoTemplate.aggregate(agg, "tags", TagCount.class);
List<TagCount> tagCount = results.getMappedResults();

score 5 · Answer 4 · answered Jul 23 '19 at 15:25

As per the answer https://stackoverflow.com/a/39784851/4546949 I wrote code for Java.

Use aggregation group to get count and array of data with other paging information.

    AggregationOperation group = Aggregation.group().count().as("total")
            .addToSet(pageable.getPageNumber()).as("pageNumber")
            .addToSet(pageable.getPageSize()).as("pageSize")
            .addToSet(pageable.getOffset()).as("offset")
            .push("$$ROOT").as("data");

Use Aggregation project to slice as per the paging information.

    AggregationOperation project = Aggregation.project()
            .andInclude("pageSize", "pageNumber", "total", "offset")
            .and(ArrayOperators.Slice.sliceArrayOf("data").offset((int) pageable.getOffset()).itemCount(pageable.getPageSize()))
            .as("data");

Use mongo template to aggregate.

    Aggregation aggr = newAggregation(group, project);
    CustomPage page = mongoTemplate.aggregate(aggregation, Foo.class, CustomPage.class).getUniqueMappedResult();

Create a CustomPage.

    public class CustomPage {
        private long pageSize;
        private long pageNumber;
        private long offset;
        private long total;
        private List<Foo> data;
    }

nyxz · Answer 5 · 2020-06-16T20:55:49.030

Here is my generic solution:

public Page<ResultObject> list(Pageable pageable) {
    // build your main stages
    List<AggregationOperation> mainStages = Arrays.asList(match(....), group(....));
    return pageAggregation(pageable, mainStages, "target-collection", ResultObject.class);
}

public <T> Page<T> pageAggregation(
        final Pageable pageable,
        final List<AggregationOperation> mainStages,
        final String collection,
        final Class<T> clazz) {
    final List<AggregationOperation> stagesWithCount = new ArrayList<>(mainStages);
    stagesWithCount.add(count().as("count"));
    final Aggregation countAgg = newAggregation(stagesWithCount);
    final Long count = Optional
            .ofNullable(mongoTemplate.aggregate(countAgg, collection, Document.class).getUniqueMappedResult())
            .map(doc -> ((Integer) doc.get("count")).longValue())
            .orElse(0L);

    final List<AggregationOperation> stagesWithPaging = new ArrayList<>(mainStages);
    stagesWithPaging.add(sort(pageable.getSort()));
    stagesWithPaging.add(skip(pageable.getOffset()));
    stagesWithPaging.add(limit(pageable.getPageSize()));
    final Aggregation resultAgg = newAggregation(stagesWithPaging);
    final List<T> result = mongoTemplate.aggregate(resultAgg, collection, clazz).getMappedResults();

    return new PageImpl<>(result, pageable, count);
}

Excellent work! Works just fine, except one thing: the pageable.getSort() is not always set so better put it into IF: if(pageable.getSort().isSorted()) { stagesWithPaging.add(Aggregation.sort(pageable.getSort())); } — vbg, Nov 05 '21 at 17:07

score 3 · Answer 6 · answered Apr 09 '20 at 14:16

To return a Paged Object with correct value of pageable object , I find this is the best and simple way.

Aggregation aggregation = Aggregation.newAggregation(Aggregation.match(Criteria.where("type").is("project")),
                        Aggregation.group("id").last("id").as("id"), Aggregation.project("id"),
                        Aggregation.skip(pageable.getPageNumber() * pageable.getPageSize()),
                        Aggregation.limit(pageable.getPageSize()));


    PageableExecutionUtils.getPage(mongoTemplate.aggregate(aggregation, Draft.class, Draft.class).getMappedResults(), pageable,() -> mongoTemplate.count(Query.of(query).limit(-1).skip(-1), Draft.class));

score 0 · Answer 7 · answered Nov 18 '20 at 12:31

Another approach would be to extend the PagingAndSortingRepository<T, ID> interface. Then, you can create an @Aggregation query method like this:

@Aggregation(pipeline = {
      "{ $match: { someField: ?0 } }",
      "{ $project: { _id: 0, someField: 1} }"
})
List<StuffAggregateModel> aggregateStuff(final String somePropertyName, final Pageable pageable);

Just call this from your business logic service class and construct the Pageable (which also contains sort options, if desired) and call the repo method. I like this approach because of the simplicity and the sheer minimization of the amount of code that you have to write. If your query (aggregation pipeline) is simple enough, this is probably the best solution. Maintenance coding for this approach is nearly effortless.

Yes, that look simple. Although this sample is applicable only to the aggregations where all input parameters are present at the moment of the request. When you build the aggregation pipeline with several optional inputs then your pipeline would look differently depends on how many inputs you have. The only way to implement such pipeline would be Spring Java SDK for MongoDB which doesn't have Pageable support out of the box. — vbg, Nov 05 '21 at 15:24

score 0 · Answer 8 · answered Sep 19 '21 at 14:05

My answer with MongoDB $facet

// User(_id, first name, etc), Car (user_id, brand, etc..)
LookupOperation lookupStageCar = Aggregation.lookup(‘cars ’, ‘user_id’, ‘_id’, ‘car’);
 MatchOperation matchStage = Aggregation.match(Criteria.where(‘car.user_id ‘).exists(true));

 CountOperation countOperation = Aggregation.count().as("total");
 AddFieldsOperation addFieldsOperation = Aggregation.addFields().addFieldWithValue("page", pageable.getPageNumber()).build();
 SkipOperation skipOperation = Aggregation.skip(Long.valueOf(pageable.getPageNumber() * pageable.getPageSize()));
 LimitOperation limitOperation = Aggregation.limit(pageable.getPageSize());

// here the magic
 FacetOperation facetOperation = Aggregation.facet( countOperation, addFieldsOperation).as("metadata")
         .and(skipOperation, limitOperation).as("data");

// users with car
 List<AggrigationResults> map = mongoTemplate.aggregate(Aggregation.newAggregation( lookupStageCar, matchStage, facetOperation), "User",  AggrigationResults.class).getMappedResults();

———————————————————————————
public class AggrigationResults  {

    private List<Metadata> metadata;
    private List<User> data;

}

public class Metadata {

    private long total;
    private long page;

}

———————————————————————————
output: 
{
    "metadata" : [ 
        {
            "total" : 300,
            "page" : 3
        }
    ],
    "data" : [ 
        {
            ... original document ...
        }, 
        {
            ... another document ...
        }, 
        {
            ... etc up to 10 docs ...
        }
    ]
}

see : How to use MongoDB aggregation for pagination?

In spring data mongodb how to achieve pagination for aggregation

8 Answers8

Linked