How to count documents Grouped by a field in MongoDB

Count documents Grouped by field in MongoDB

In MongoDB, to count documents grouped by a specific field (similar to SQL's SELECT COUNT GROUP BY), you can use the aggregation framework. This approach allows you to group documents by a field and then count how many documents fall under each group.

Description:

To get a count of documents grouped by a particular field in MongoDB, use an aggregation pipeline with the $group operator. This will help you organize documents by the specified field and return the count of documents within each group. This is commonly used for tasks such as counting the number of documents per category or status.

Syntax:

In MongoDB, you can achieve a COUNT GROUP BY operation with this aggregation syntax:

db.<collection>.aggregate([
  { $group: { _id: "$<field>", count: { $sum: 1 } } }
])

Explanation:

• <collection>: The collection you want to query.
• <field>: The field by which you want to group documents.

Example:

Suppose you have a sales collection, and each document has a category field. To count the number of sales documents per category, you can use:

Code:

// Aggregate query to count documents grouped by the 'category' field
db.sales.aggregate([
  // Group by the 'category' field
  { $group: { 
      _id: "$category", // Group by the 'category' field
      count: { $sum: 1 } // Count each document in the group
    } 
  }
])

Explanation:

• db.sales.aggregate(...)
• Runs an aggregation pipeline on the sales collection to count documents grouped by category.
• { $group: { _id: "$category", count: { $sum: 1 } } }
• The $group stage groups documents by the specified field, in this case, category.
• _id: "$category": Sets the _id of each group to the unique values of the category field.
• count: { $sum: 1 }: Counts each document in the group by adding 1 for every document found. This provides the total count for each category.

Additional Notes:

• Multiple Group Fields: You can also group by multiple fields by setting _id as an object, e.g., _id: { category: "$category", status: "$status" }.
• $sort and $limit: To refine results, use $sort or $limit in your pipeline to order or cap results based on the group count.

Example Output:

The aggregation will return an array with documents structured as follows:

 [
  { "_id": "Electronics", "count": 10 },
  { "_id": "Furniture", "count": 5 },
  { "_id": "Clothing", "count": 20 }
]

This output shows the count of documents for each unique category value.