How to paginate aggregation query results in MongoDB and get a total document count (Node.js + Mongoose)?

Question

I want to paginate my query results via $limit and $skip and also get the total count of documents passed the query.

What I do now to paginate results is this:

  const startIndex = (page - 1) * limit
  const endIndex = page * limit
  const resultObject = {}

  resultObject.totalCount = await model.countDocuments(query).exec()

  if (endIndex < results.totalCount)
    resultObject.next = {page: page + 1, limit}

  if (startIndex > 0)
    results.prev = {page: page - 1, limit}

  resultObject.results = await query.limit(limit).skip(startIndex).exec()

This produces 2 problems:

• I execute the query twice (can you even do this in 1?)
• For some reason when my query is an aggregation, countDocuments returns 0 even tho when I execute query.exec() I get the correct results (> 0)

So my question is how can i paginate the results like i do now only with 1 query (and make it work with aggregation queries) while getting the document count so i can build my resultObject

Answer 1

• "I execute the query twice (can you even do this in 1?)" No you can't, the only way to achieve this is to fetch the entire collection and then in code return required documents which is obviously a pretty bad solution.

• Now this is a little tricky, technically countDocuments is a wrapper that just executes a pipeline:

The method wraps the $group aggregation stage with a $sum expression to perform the count

Meaning

db.collection.countDocuments({});

Is equal to:

db.collection.aggregate([
    {
        $group: {
            _id: null,
            count: {$sum: 1}
        }
    }
]);

However different drivers support this command differently, for example the nodejs Mongo driver doesn't allow certain expressions to be used as specified here. You're also using mongoose which might have additional changes.

Without looking at your exact query it's hard to say why it's failing however if you fail to find the reason what I recommend is just execute the wrapped pipeline instead.

Answer 2

I found a way to get a query's result AND the count all in the same "trip" to the DB (still 2 queries but within the database so it is much faster)

The key is to use $facet (mongodb 3.4+):

const pipeline = [
  {YOUR_QUERY},
  {
    $facet: {
      paginatedResults: [{$skip: startIndex}, {$limit: limit}],
      totalCount: [{$count: 'count'}]
    }
  },
  {
    $addFields: {
      total: {
        $ifNull: [{ $arrayElemAt: ['$totalCount.count', 0]}, 0]
      }
    }
  },
  {
    $project: {
      paginatedResults: 1,
      total: 1
    }
  }
]

explanation: $facet executes multiple pipelines on the same input (our query result) we can have 1 pipeline for paginating the results and 1 for counting the total.

Since $count returns an object containing the count field, and facet wraps in an array, the $addFields pipeline extracts the actual count value as a total field. Then I just use $project to remove the no longer needed totalCount and that's the result:

  {
     paginatedResults: [RESULTS],
     total: 34
  }