39

I want to perform an aggregation query that does basic pagination:

  1. Find all orders that belongs to a certain company_id
  2. Sort the orders by order_number
  3. Count the total number of documents
  4. Skips to e.g. document number 100 and passes on the rest
  5. Limits the number of documents to e.g. 2 and passes them on
  6. Finishes by returning the count and a selected few fields from the documents

Here is a breakdown of the query:

db.Order.collection.aggregate([

This finds all matching documents:

  { '$match'    : { "company_id" : ObjectId("54c0...") } },

This sorts the documents:

  { '$sort'     : { 'order_number' : -1 } },

This counts the documents and passes the unmodified documents, but I'm sure doing it wrong, because things turn weird from here:

  {
    '$group' : {
      '_id'     : null,
      'count'   : { '$sum' : 1 },
      'entries' : { '$push' : "$$ROOT" }
    }
  },

This seems to skip some documents:

  { "$skip"     : 100 },

This is supposed to limit the documents, but it does not:

  { "$limit"    : 2 },

This does return the count, but it does not return the documents in an array, instead it returns arrays with each field:

  { '$project'  : {
      'count'     : 1,
      'entries'   : {'_id' : "$entries._id", 'order_number' : "$entries.order_number"}
    }
  }
])

This is the result:

[
  { "_id" : null,
    "count" : 300,
    "entries" : [
      {
        "_id" : [ObjectId('5a5c...'), ObjectId('5a5c...')],
        "order_number" : ["4346", "4345"]
      },
      {
        "_id" : [ObjectId('5a5c...'), ObjectId('5a5c...')],
        "order_number" : ["4346", "4345"]
      },
      ...
    ]
  }
]

Where do I get it wrong?

JohnSmith1976
  • 536
  • 2
  • 12
  • 35
  • The $group stage returns you a single document. If you want to paginate and calculate total in a "single" query you need to use [facets](https://docs.mongodb.com/manual/reference/operator/aggregation/facet/index.html) – Alex Blex Jan 17 '18 at 16:37
  • You are missing $unwind stage after $group to flatten the documents. Try adding {$unwind:"$entries"} after group stage. More [here](https://stackoverflow.com/q/48288837/2683814) – s7vr Jan 17 '18 at 16:38
  • @AlexBlex I would need an elaboration in order to understand that. @Veeram I tried your suggestion, and it certainly did improve the result, but it puts `"_id":null` and `"count":300` into every array entry, thus causing the response to be larger than necessary: `[{"_id":null, "count":300, "entries":{"_id":ObjectId('5a5c...'), "order_number":"4346"}}, {"_id":null, "count":300, "entries":{"_id":ObjectId('5a5c...'), "order_number":"4345"}}, ...]` – JohnSmith1976 Jan 17 '18 at 16:51
  • That is expected. You just need to shape the results to retain what you need at the end. Add the `$project/$addFields` stage as the last stage to keep what you want. Btw why are you counting the results if you don't want to retain the count field ? What is your expected output ? – s7vr Jan 17 '18 at 16:58

2 Answers2

105

To calculate totals and return a subset, you need to apply grouping and skip/limit to the same dataset. For that you can utilise facets

For example to show 3rd page, 10 documents per page:

db.Order.aggregate([
    { '$match'    : { "company_id" : ObjectId("54c0...") } },
    { '$sort'     : { 'order_number' : -1 } },
    { '$facet'    : {
        metadata: [ { $count: "total" }, { $addFields: { page: NumberInt(3) } } ],
        data: [ { $skip: 20 }, { $limit: 10 } ] // add projection here wish you re-shape the docs
    } }
] )

It will return a single document with 2 fields:

{
    "metadata" : [ 
        {
            "total" : 300,
            "page" : 3
        }
    ],
    "data" : [ 
        {
            ... original document ...
        }, 
        {
            ... another document ...
        }, 
        {
            ... etc up to 10 docs ...
        }
    ]
}
Alex Blex
  • 34,704
  • 7
  • 48
  • 75
  • Thanks @AlexBlex, will try this out, but first I'm struggling to understand what `$skip: 20` does. You are defining `page: NumberInt(3)` under `metadata`, which matches that your example looks for the 3rd page. So I don't understand the `$skip: 20`. – JohnSmith1976 Jan 17 '18 at 19:28
  • 5
    @JohnSmith1976 it's just an example. Assuming 10 docs per page. 1st page return first 10 documents. For that you will skip 0. For n-th page you skip `(n-1)*pageSize`, so for 3rd one it is `(3-1)*10 = 20` – Alex Blex Jan 17 '18 at 19:42
  • Is the sorting required for the pagination? Are the aggregated results stable and suitable for pagination if not sorted? – Hlorofos Jun 25 '18 at 09:26
  • 1
    @Hlorofos, No sort is not required, but there were some problems with explicit natural order in aggregation at some point, so I would recommend to sort it. Not sure what "stable" is, but if you are asking if pagination breaks on insertion/deletion of documents from the collection, it does. Each new query is a new query. It re-counts number of available documents each time, and does skip and limit as requested. There is no magic here. – Alex Blex Jun 25 '18 at 09:55
  • @AlexBlex thanks for your comment! I meant when there are no insert or update operations - is there some optimizations which might change the aggregated list items order when there is no explicit sort operation applied. – Hlorofos Jun 26 '18 at 19:08
  • 1
    @Hlorofos, I see, good point. You should be safe if the $match stage uses an index implicitly. I'd take extra care for sharded collections where $match doesn't use shard key. The merging shard assemble results of cursors from other shards, so it might be a good candidate for the inconsistency you are talking about. Couldn't confirm it with my quick tests, but it proves nothing. – Alex Blex Jun 28 '18 at 10:15
  • @AlexBlex How to print the result in console.log(); I got this after print gth: 0, pipes: null, pipesCount: 0, flowing: null, ended: false, endEmitted: false, reading: false, sync: true, needReadable: false, emittedReadable: false, readableListening: false, resumeScheduled: false, – Taha Farooqui Apr 11 '20 at 16:27
  • You should note that `$facet` won't work below mongodb version 3.4: https://docs.mongodb.com/manual/reference/operator/aggregation/facet/ – Taha Paksu Jun 09 '20 at 10:29
  • Thanks @TahaPaksu, You are right. I would only add that v3.4 was decommissioned half-year ago and nobody should work with it anymore. If you still do I would strongly recommend to upgrade. – Alex Blex Jun 09 '20 at 10:52
  • @AlexBlex that won't be an issue but it's always good to have extra information for people looking for "why isn't this working?" kind of answers. We're still using 3.2 which I'm forcing them to upgrade with no luck :) – Taha Paksu Jun 09 '20 at 10:57
  • is there any way that I can use that `total` value from `metadata` in order to calculate the total page?! – chichi Dec 09 '21 at 13:18
8

Since mongoDB version 5.0 there is another option, that allows to avoid the disadvantage of $facet, the grouping of all returned document into a one big document. The main concern is that a document as a size limit of 16M. Using $setWindowFields allows to avoid this concern:

db.Order.aggregate([
    {$match: {company_id: ObjectId("54c0...") } },
    {$sort: {order_number: -1 } },
    {$setWindowFields: {output: {totalCount: {$count: {}}}}}
    {$skip: 20 },
    {$limit: 10 } 
])
nimrod serok
  • 14,151
  • 2
  • 11
  • 33