Mongodb aggregation $group followed by $limit for pagination

Question

In MongoDB aggregation pipeline, record flow from stage to stage happens one/batch at a time (or) will wait for the current stage to complete for whole collection before passing it to next stage?

For e.g., I have a collection classtest with following sample records

{name: "Person1", marks: 20}
{name: "Person2", marks: 20}
{name: "Person1", marks: 20}

I have total 1000 records for about 100 students and I have following aggregate query

    db.classtest.aggregate(
[
    {$sort: {name: 1}},
    {$group: {_id: '$name',
            total: {$sum: '$marks'}}},
    {$limit: 5}
])

I have following questions.

1. The sort order is lost in final results. If I place another sort after $group, then results are sorted properly. Does that mean $group not maintains the previous sort order?
2. I would like to limit the results to 5. Does group operation has to be completely done (for all 1000 records) before passing to the limit. (or) The group operation passes the records to limit stage as and when it has record and stops processing when the requirement for limit stage is met?

My actual idea is to do pagination on results of aggregate. In above scenario, if $group maintains sort order and processes only required number of records, I want to apply $match condition {$ge: 'lastPersonName'} in subsequent page queries.

1. I do not want to apply $limit before $group as I want results for 5 students not first 5 records.
2. I may not want to use $skip as that means effectively traversing those many records.

Answer 1

I have solved the problem without need of maintaining another collection or even without $group traversing whole collection, hence posting my own answer.

As others have pointed:

1. $group doesn't retain order, hence early sorting is not of much help.
2. $group doesn't do any optimization, even if there is a following $limit, i.e., runs $group on entire collection.

My usecase has following unique features, which helped me to solve it:

1. There will be maximum of 10 records per each student (minimum of 1).

2. I am not very particular on page size. The front-end capable of handling varying page sizes. The following is the aggregation command I have used.

   db.classtest.aggregate(
   [
       {$sort: {name: 1}},
       {$limit: 5 * 10},
       {$group: {_id: '$name',
           total: {$sum: '$marks'}}},
       {$sort: {_id: 1}}
   ])
   

Explaining the above.

1. if $sort immediately precedes $limit, the framework optimizes the amount of data to be sent to next stage. Refer here
2. To get a minimum of 5 records (page size), I need to pass at least 5 (page size) * 10 (max records per student) = 50 records to the $group stage. With this, the size of final result may be anywhere between 0 and 50.
3. If the result is less than 5, then there is no further pagination required.
4. If the result size is greater than 5, there may be chance that last student record is not completely processed (i.e., not grouped all the records of student), hence I discard the last record from the result.

5. Then name in last record (among retained results) is used as $match criteria in subsequent page request as shown below.

   db.classtest.aggregate(
   [
       {$match: {name: {$gt: lastRecordName}}}
       {$sort: {name: 1}},
       {$limit: 5 * 10},
       {$group: {_id: '$name',
           total: {$sum: '$marks'}}},
       {$sort: {_id: 1}}
   ])
   

In above, the framework will still optimize $match, $sort and $limit together as single operation, which I have confirmed through explain plan.

Answer 2

The first few things to consider here is that the aggregation framework works with a "pipeline" of stages to be applied in order to get a result. If you are familiar with processing things on the "command line" or "shell" of your operating system, then you might have some experience with the "pipe" or | operator.

Here is a common unix idiom:

ps -ef | grep mongod | tee "out.txt"

In this case the output of the first command here ps -ef is being "piped" to the next command grep mongod which in turn has it's output "piped" to the tee out.txt which both outputs to terminal as well as the specified file name. This is a "pipeline" wher each stage "feeds" the next, and in "order" of the sequence they are written in.

The same is true of the aggregation pipeline. A "pipeline" here is in fact an "array", which is an ordered set of instructions to be passed in processing the data to a result.

db.classtest.aggregate([
    { "$group": {
      "_id": "$name",
      "total": { "$sum": "$marks"}
    }},
    { "$sort": { "name": 1 } },
    { "$limit": 5 }
])  

So what happens here is that all of the items in the collection are first processed by $group to get their totals. There is no specified "order" to grouping so there is not much sense in pre-ordering the data. Neither is there any point in doing so because you are yet to get to your later stages.

Then you would $sort the results and also $limit as required.

For your next "page" of data you will want ideally $match on the last unique name found, like so:

db.classtest.aggregate([
    { "$match": { "name": { "$gt": lastNameFound } }},
    { "$group": {
      "_id": "$name",
      "total": { "$sum": "$marks"}
    }},
    { "$sort": { "name": 1 } },
    { "$limit": 5 }
])  

It's not the best solution, but there really are not alternatives for this type of grouping. It will however notably get "faster" with each iteration towards the end. Alternately, storing all the unqiue names ( or reading that out of another collection ) and "paging" through that list with a "range query" on each aggregation statement may be a viable option, if your data permits it.

Something like:

db.classtest.aggregate([
    { "$match": { "name": { "$gte": "Allan", "$lte": "David" } }},
    { "$group": {
      "_id": "$name",
      "total": { "$sum": "$marks"}
    }},
    { "$sort": { "name": 1 } },
])  

Unfortunately there is not a "limit grouping up until x results" option, so unless you can work with another list, then you are basically grouping up everything ( and possibly a a gradually smaller set each time ) with each aggregation query you send.

Answer 3

1. "$group does not order its output documents." See http://docs.mongodb.org/manual/reference/operator/aggregation/group/

2. $limit limits the number of processed elements of an immediately preceding $sort operation, not only the number of elements passed to the next stage. See the note at http://docs.mongodb.org/manual/reference/operator/aggregation/limit/

For the very first question you asked, I am not sure, but it appears (see 1.) that a stage n+1 can influence the behaviour of stage n : the limit will limit the sort operation to its first n elements, and the sort operation will not complete just as if the following limit stage did not exist.

Answer 4

pagination on group data mongodb -

in $group items you can't directly apply pagination, but below trick will be used ,

if you want pagination on group data -

for example- i want group products categoryWise and then i want only 5 product per category then

step 1 - write aggregation on product table, and write groupBY

        { $group: { _id: '$prdCategoryId', products: { $push: '$$ROOT' } } },

step 2 - prdSkip for skipping , and limit for limiting data , pass it dynamically

        {
            $project: {
                // pagination for products
                products: {
                    $slice: ['$products', prdSkip, prdLimit],
                }
            }
        },

finally query looks like - params - limit , skip - for category pagination and prdSkip and PrdLimit for products pagination

    db.products.aggregate([

        { $group: { _id: '$prdCategoryId', products: { $push: '$$ROOT' } } },
        {
            $lookup: {
                from: 'categories',
                localField: '_id',
                foreignField: '_id',
                as: 'categoryProducts',
            },
        },
        {
            $replaceRoot: {
                newRoot: {
                    $mergeObjects: [{ $arrayElemAt: ['$categoryProducts', 0] }, '$$ROOT'],
                },
            },
        },
        {
            $project: {
                // pagination for products
                products: {
                    $slice: ['$products', prdSkip, prdLimit],
                },
                _id: 1,
                catName: 1,
                catDescription: 1,
            },
        },
    ])
    .limit(limit) // pagination for category
    .skip(skip);

I used replaceRoot here to pullOut category.