MongoDB Aggregation: How to get total records count?
MongoDB Aggregation: Efficiently Counting Total Records
Learn various methods to get the total record count in MongoDB aggregation pipelines, from basic $count to more complex scenarios involving $facet.
When working with MongoDB, especially within aggregation pipelines, a common requirement is to determine the total number of documents that match certain criteria or have passed through a specific stage. This article explores different techniques to achieve an accurate record count, catering to various use cases and complexities.
Basic Count with $count Stage
The simplest and most efficient way to get a total count of documents in an aggregation pipeline is by using the $count stage. This stage takes no arguments and outputs a single document with a field containing the count of documents that have passed through the previous stages.
db.collection.aggregate([
{ $match: { status: "active" } },
{ $count: "totalActiveRecords" }
]);
Using $count to get the total number of active records.
In this example, the pipeline first filters documents where the status field is "active", and then $count tallies these filtered documents, outputting a result like [ { "totalActiveRecords" : 123 } ].
$count stage should generally be placed as late as possible in your pipeline if you are filtering documents, as it operates on the documents it receives. If you need to count all documents in a collection, you can use db.collection.countDocuments({}) directly, which is often more efficient than an aggregation pipeline for a full collection count.Counting Within a $facet Stage for Combined Results
Sometimes, you need to perform multiple aggregations on the same input documents, including getting a total count, without having to run separate queries. The $facet stage allows you to run multiple independent sub-pipelines on the same set of input documents. This is particularly useful for building dashboards or reports where you need different views of the data, including a total count, in a single query.
graph TD
A[Input Documents] --> B{$facet}
B --> C1[Sub-pipeline 1: Filter & Count]
B --> C2[Sub-pipeline 2: Group & Sum]
C1 --> D1[Result: Total Count]
C2 --> D2[Result: Aggregated Data]
D1 & D2 --> E[Combined Output]Conceptual flow of using $facet for parallel aggregations, including counting.
db.orders.aggregate([
{ $match: { orderDate: { $gte: new Date('2023-01-01') } } },
{ $facet: {
"totalOrders": [
{ $count: "count" }
],
"ordersByStatus": [
{ $group: { _id: "$status", total: { $sum: 1 } } },
{ $sort: { total: -1 } }
]
}
}
]);
Using $facet to get total orders and orders grouped by status in one query.
In this $facet example, the totalOrders sub-pipeline simply uses $count to get the total number of orders matching the initial $match criteria. The ordersByStatus sub-pipeline groups and counts orders by their status. The output will be a single document containing both results:
[
{
"totalOrders": [ { "count": 500 } ],
"ordersByStatus": [
{ "_id": "completed", "total": 300 },
{ "_id": "pending", "total": 150 },
{ "_id": "cancelled", "total": 50 }
]
}
]
Alternative: $group with $sum
Before $count was introduced, or in scenarios where you need to perform other aggregations alongside counting, you might use a $group stage with $sum: 1. This approach groups all documents into a single group and sums 1 for each document, effectively counting them.
db.collection.aggregate([
{ $match: { category: "electronics" } },
{ $group: { _id: null, totalRecords: { $sum: 1 } } }
]);
Counting documents using $group and $sum: 1.
Here, _id: null ensures all documents are grouped into a single group. While functional, $count is generally preferred for simple counting due to its conciseness and often better performance.
$group with $sum: 1 works, $count is specifically optimized for counting and is the recommended approach for obtaining a simple document count within an aggregation pipeline.