MongoDB Query to Find Distinct Values with Count

Use MongoDB's aggregation pipeline with $group to group by the field and count occurrences, like db.collection.aggregate([{ $group: { _id: "$field", count: { $sum: 1 } } }]) to get distinct values with their counts.

Examples

Input[{ name: "Alice" }, { name: "Bob" }, { name: "Alice" }]

Output[{ _id: "Alice", count: 2 }, { _id: "Bob", count: 1 }]

Input[{ category: "Fruit" }, { category: "Vegetable" }, { category: "Fruit" }, { category: "Fruit" }]

Output[{ _id: "Fruit", count: 3 }, { _id: "Vegetable", count: 1 }]

Input[]

Output[]

How to Think About It

To find distinct values with their counts, think of grouping all documents by the field you want distinct values from, then count how many documents fall into each group. This is done by using $group in MongoDB aggregation, which collects documents by a key and can sum counts.

Algorithm

1

Select the collection to query.

2

Group documents by the target field using <code>$group</code>.

3

For each group, count the number of documents using <code>$sum: 1</code>.

4

Return the grouped distinct values with their counts.

Code

mongodb

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

Output

[
  { _id: "value1", count: 5 },
  { _id: "value2", count: 3 },
  { _id: "value3", count: 2 }
]

Dry Run

Let's trace grouping and counting distinct values for field 'color' in sample documents.

1

Group by 'color'

Documents: [{color: 'red'}, {color: 'blue'}, {color: 'red'}, {color: 'green'}, {color: 'blue'}]

2

Count each group

Groups: 'red' -> 2, 'blue' -> 2, 'green' -> 1

color	count
red	2
blue	2
green	1

Why This Works

Step 1: Grouping documents

The $group stage groups documents by the specified field, creating buckets for each distinct value.

Step 2: Counting documents per group

Using $sum: 1 adds 1 for each document in the group, giving the count of occurrences.

Alternative Approaches

Using distinct and then counting in application

mongodb

const distinctValues = await db.collection.distinct('field');
const counts = {};
for (const val of distinctValues) {
  counts[val] = await db.collection.countDocuments({ field: val });
}
console.log(counts);

This method requires multiple queries and is less efficient than aggregation.

Aggregation with $sortByCount

mongodb

db.collection.aggregate([
  { $sortByCount: "$field" }
])

<code>$sortByCount</code> is a shortcut for grouping and counting, returning distinct values with counts sorted by count.

Complexity: O(n) time, O(k) space

Time Complexity

The aggregation scans all documents once, so time is proportional to the number of documents, O(n).

Space Complexity

Space depends on the number of distinct values k, as groups are stored in memory, O(k).

Which Approach is Fastest?

Aggregation with $group or $sortByCount is faster and more efficient than multiple queries using distinct() and countDocuments().

Approach	Time	Space	Best For
Aggregation with $group	O(n)	O(k)	Efficient counting of distinct values
Aggregation with $sortByCount	O(n)	O(k)	Concise syntax with sorting by count
distinct() + countDocuments()	O(n*k)	O(k)	Simple but inefficient for large data