[MongoDB] Calculate unique values using distinct and aggregate

Tadashi Shigeoka ·  Sat, June 22, 2019

This article introduces sample code for calculating unique values in MongoDB using distinct and aggregate.

MongoDB | モンゴディービー

Sample Code for Calculating unique Values in MongoDB

Calculate unique users with distinct

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

Calculate unique users with aggregate

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.