MongoDB Aggregation: How to get total records count?

Since v.3.4 (i think) MongoDB has now a new aggregation pipeline operator named 'facet' which in their own words:

Processes multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its results are stored as an array of documents.

In this particular case, this means that one can do something like this:

$result = $collection->aggregate([
  { ...execute queries, group, sort... },
  { ...execute queries, group, sort... },
  { ...execute queries, group, sort... },
  {
    $facet: {
      paginatedResults: [{ $skip: skipPage }, { $limit: perPage }],
      totalCount: [
        {
          $count: 'count'
        }
      ]
    }
  }
]);

The result will be (with for ex 100 total results):

[
  {
    "paginatedResults":[{...},{...},{...}, ...],
    "totalCount":[{"count":100}]
  }
]

This is one of the most commonly asked question to obtain the paginated result and the total number of results simultaneously in single query. I can't explain how I felt when I finally achieved it LOL.

$result = $collection->aggregate(array(
  array('$match' => $document),
  array('$group' => array('_id' => '$book_id', 'date' => array('$max' => '$book_viewed'),  'views' => array('$sum' => 1))),
  array('$sort' => $sort),

// get total, AND preserve the results
  array('$group' => array('_id' => null, 'total' => array( '$sum' => 1 ), 'results' => array( '$push' => '$$ROOT' ) ),
// apply limit and offset
  array('$project' => array( 'total' => 1, 'results' => array( '$slice' => array( '$results', $skip, $length ) ) ) )
))

Result will look something like this:

[
  {
    "_id": null,
    "total": ...,
    "results": [
      {...},
      {...},
      {...},
    ]
  }
]

Use this to find total count in resulting collection.

db.collection.aggregate( [
{ $match : { score : { $gt : 70, $lte : 90 } } },
{ $group: { _id: null, count: { $sum: 1 } } }
] );

You can use toArray function and then get its length for total records count.

db.CollectionName.aggregate([....]).toArray().length

Here are some ways to get total records count while doing MongoDB Aggregation:

• Using $count:

  db.collection.aggregate([
     // Other stages here
     { $count: "Total" }
  ])
  

  For getting 1000 records this takes on average 2 ms and is the fastest way.

• Using .toArray():

  db.collection.aggregate([...]).toArray().length
  

  For getting 1000 records this takes on average 18 ms.

• Using .itcount():

  db.collection.aggregate([...]).itcount()
  

  For getting 1000 records this takes on average 14 ms.

Use the $count aggregation pipeline stage to get the total document count:

Query :

db.collection.aggregate(
  [
    {
      $match: {
        ...
      }
    },
    {
      $group: {
        ...
      }
    },
    {
      $count: "totalCount"
    }
  ]
)

Result:

{
   "totalCount" : Number of records (some integer value)
}

I did it this way:

db.collection.aggregate([
     { $match : { score : { $gt : 70, $lte : 90 } } },
     { $group: { _id: null, count: { $sum: 1 } } }
] ).map(function(record, index){
        print(index);
 });

The aggregate will return the array so just loop it and get the final index .

And other way of doing it is:

var count = 0 ;
db.collection.aggregate([
{ $match : { score : { $gt : 70, $lte : 90 } } },
{ $group: { _id: null, count: { $sum: 1 } } }
] ).map(function(record, index){
        count++
 }); 
print(count);

//const total_count = await User.find(query).countDocuments();
//const users = await User.find(query).skip(+offset).limit(+limit).sort({[sort]: order}).select('-password');
const result = await User.aggregate([
  {$match : query},
  {$sort: {[sort]:order}},
  {$project: {password: 0, avatarData: 0, tokens: 0}},
  {$facet:{
      users: [{ $skip: +offset }, { $limit: +limit}],
      totalCount: [
        {
          $count: 'count'
        }
      ]
    }}
  ]);
console.log(JSON.stringify(result));
console.log(result[0]);
return res.status(200).json({users: result[0].users, total_count: result[0].totalCount[0].count});

Solution provided by @Divergent does work, but in my experience it is better to have 2 queries:

1. First for filtering and then grouping by ID to get number of filtered elements. Do not filter here, it is unnecessary.
2. Second query which filters, sorts and paginates.

Solution with pushing $$ROOT and using $slice runs into document memory limitation of 16MB for large collections. Also, for large collections two queries together seem to run faster than the one with $$ROOT pushing. You can run them in parallel as well, so you are limited only by the slower of the two queries (probably the one which sorts).

I have settled with this solution using 2 queries and aggregation framework (note - I use node.js in this example, but idea is the same):

var aggregation = [
  {
    // If you can match fields at the begining, match as many as early as possible.
    $match: {...}
  },
  {
    // Projection.
    $project: {...}
  },
  {
    // Some things you can match only after projection or grouping, so do it now.
    $match: {...}
  }
];


// Copy filtering elements from the pipeline - this is the same for both counting number of fileter elements and for pagination queries.
var aggregationPaginated = aggregation.slice(0);

// Count filtered elements.
aggregation.push(
  {
    $group: {
      _id: null,
      count: { $sum: 1 }
    }
  }
);

// Sort in pagination query.
aggregationPaginated.push(
  {
    $sort: sorting
  }
);

// Paginate.
aggregationPaginated.push(
  {
    $limit: skip + length
  },
  {
    $skip: skip
  }
);

// I use mongoose.

// Get total count.
model.count(function(errCount, totalCount) {
  // Count filtered.
  model.aggregate(aggregation)
  .allowDiskUse(true)
  .exec(
  function(errFind, documents) {
    if (errFind) {
      // Errors.
      res.status(503);
      return res.json({
        'success': false,
        'response': 'err_counting'
      });
    }
    else {
      // Number of filtered elements.
      var numFiltered = documents[0].count;

      // Filter, sort and pagiante.
      model.request.aggregate(aggregationPaginated)
      .allowDiskUse(true)
      .exec(
        function(errFindP, documentsP) {
          if (errFindP) {
            // Errors.
            res.status(503);
            return res.json({
              'success': false,
              'response': 'err_pagination'
            });
          }
          else {
            return res.json({
              'success': true,
              'recordsTotal': totalCount,
              'recordsFiltered': numFiltered,
              'response': documentsP
            });
          }
      });
    }
  });
});

I needed the absolute total count after applying the aggregation. This worked for me:

db.mycollection.aggregate([
    {
        $group: { 
            _id: { field1: "$field1", field2: "$field2" },
        }
    },
    { 
        $group: { 
            _id: null, count: { $sum: 1 } 
        } 
    }
])

Result:

{
    "_id" : null,
    "count" : 57.0
}

This could be work for multiple match conditions

            const query = [
                {
                    $facet: {
                    cancelled: [
                        { $match: { orderStatus: 'Cancelled' } },
                        { $count: 'cancelled' }
                    ],
                    pending: [
                        { $match: { orderStatus: 'Pending' } },
                        { $count: 'pending' }
                    ],
                    total: [
                        { $match: { isActive: true } },
                        { $count: 'total' }
                    ]
                    }
                },
                {
                    $project: {
                    cancelled: { $arrayElemAt: ['$cancelled.cancelled', 0] },
                    pending: { $arrayElemAt: ['$pending.pending', 0] },
                    total: { $arrayElemAt: ['$total.total', 0] }
                    }
                }
                ]
                Order.aggregate(query, (error, findRes) => {})

Here is an example with Pagination, match and sort in mongoose aggregate

const [response] = await Prescribers.aggregate([
      { $match: searchObj },
      { $sort: sortObj },
      {
        $facet: {
          response: [{ $skip: count * page }, { $limit: count }],
          pagination: [
            {
              $count: 'totalDocs',
            },
            {
              $addFields: {
                page: page + 1,
                totalPages: {
                  $floor: {
                    $divide: ['$totalDocs', count],
                  },
                },
              },
            },
          ],
        },
      },
    ]);

Here count is the limit of each page and page is the the page number. Prescribers is the model

This would return the records similar to this

"data": {
    "response": [
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
    ],
    "pagination": [
        {
            "totalDocs": 592438,
            "page": 1,
            "totalPages": 59243
        }
    ]
}

It will first match and then give the matching condition total document count even your document count greater than 100k.

[
  {
    $match: {
      "Document.CompanyId": 12345
    }
  },
  {
    $count: 'Total_Count'
  }
]

If you don't want to group, then use the following method:

db.collection.aggregate( [ { $match : { score : { $gt : 70, $lte : 90 } } }, { $count: 'count' } ] );

Sorry, but I think you need two queries. One for total views and another one for grouped records.

You can find useful this answer

if you need to $match with nested documents then

https://mongoplayground.net/p/DpX6cFhR_mm

db.collection.aggregate([
  {
    "$unwind": "$tags"
  },
  {
    "$match": {
      "$or": [
        {
          "tags.name": "Canada"
        },
        {
          "tags.name": "ABC"
        }
      ]
    }
  },
  {
    "$group": {
      "_id": null,
      "count": {
        "$sum": 1
      }
    }
  }
])

I had to perform a lookup, match and then count the documents recieved. Here is how I achieved it using mongoose:

ModelName.aggregate([
  {
    '$lookup': {
      'from': 'categories', 
      'localField': 'category', 
      'foreignField': '_id', 
      'as': 'category'
    }
  }, {
    '$unwind': {
      'path': '$category'
    }
  }, {
    '$match': {
      'category.price': {
        '$lte': 3, 
        '$gte': 0
      }
    }
  }, {
    '$count': 'count'
  }
]);