[MongoDB] Calculate unique values using distinct and aggregate
This article introduces sample code for calculating unique values in MongoDB using distinct and aggregate.
Just use db.collection.distinct(‘field’) and you’re done.
Here’s a sample query to get unique users from orders:
var users = db.orders.distinct('user');
For simple conditions, using distinct is sufficient, but with a large number of documents, errors may occur.
exception: distinct too big, 16mb cap
When there are a large number of order documents that exceed the 16MB limit, the following error occurs. In this case, use aggregate for aggregation as introduced next.
> var users = db.orders.distinct('user');
2019-06-22T12:52:05.819+0000 E QUERY Error: distinct failed: {
"errmsg" : "exception: distinct too big, 16mb cap",
"code" : 17217,
"ok" : 0
}
at Error ()
at DBCollection.distinct (src/mongo/shell/collection.js:1237:15)
at (shell):1:19 at src/mongo/shell/collection.js:1237
Using aggregate won’t produce the exception: distinct too big, 16mb cap error, but the execution time for .forEach() .push() can be considerable.
var users = [];
db.orders.aggregate(
[
{
$group: { _id: '$user' }
}
]
).forEach(function(result) {
users.push(result._id);
});
That’s all from the Gemba on aggregating unique values with MongoDB queries.