[FIXED] How to use MongoDB aggregation for pagination?

Issue

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?

Solution

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 ...
        }
    ]
}

Answered By - Alex Blex

Answer Checked By - Timothy Miller (PHPFixing Admin)