MongoDB select count(distinct x) on an indexed column - count unique results for large data sets

84
votes

I have gone through several articles and examples, and have yet to find an efficient way to do this SQL query in MongoDB (where there are millions of rows documents)

First attempt

(e.g. from this almost duplicate question - Mongo equivalent of SQL's SELECT DISTINCT?)

db.myCollection.distinct("myIndexedNonUniqueField").length

Obviously I got this error as my dataset is huge

Thu Aug 02 12:55:24 uncaught exception: distinct failed: {
        "errmsg" : "exception: distinct too big, 16mb cap",
        "code" : 10044,
        "ok" : 0
}

Second attempt

I decided to try and do a group 

db.myCollection.group({key: {myIndexedNonUniqueField: 1},
                initial: {count: 0}, 
                 reduce: function (obj, prev) { prev.count++;} } );

But I got this error message instead:

exception: group() can't handle more than 20000 unique keys

Third attempt

I haven't tried yet but there are several suggestions that involve mapReduce

e.g.

Also

It seems there is a pull request on GitHub fixing the .distinct method to mention it should only return a count, but it's still open: https://github.com/mongodb/mongo/pull/34

But at this point I thought it's worth to ask here, what is the latest on the subject? Should I move to SQL or another NoSQL DB for distinct counts? or is there an efficient way?

Update:

This comment on the MongoDB official docs is not encouraging, is this accurate?

http://www.mongodb.org/display/DOCS/Aggregation#comment-430445808

Update2:

Seems the new Aggregation Framework answers the above comment... (MongoDB 2.1/2.2 and above, development preview available, not for production)

http://docs.mongodb.org/manual/applications/aggregation/

3
mongodb
I assume you need to do this frequently or performance wouldn't matter that much. In that case I'd store the distinct values in a separate collection that's updated when you insert a new document instead of trying to do a distinct on a collection that large. Either that or I'd re-evaluate my use of MongoDb and possibly move to something else. As you found, MongoDb currently isn't good at what you're trying to do. – Tim Gautier
@TimGautier thanks, I was afraid so, it took hours to insert all those values, and I should have thought of that before :) I think I'll spend the time now to insert it to MySQL for those statistics... – Eran Medan
You can also do an incremental MR basically emulating delta indexing of aggregate data. I mean it depends on when you need the results as to what you use. I can imagine that MySQL would prolly get a lot of IO and what not from doing this (I can kill a small server with distincting just 100k docs inline on an index) but I suppose it is more flexible in querying for this sort of stuff still. – Sammaye
I disagree that mongo is not good at this sort of thing. This sort if thing is what Mongo excels at. – superluminary
Unfortunately moderator deleted my answer that I also posted on duplicate question. I can't delete it there and repost here thus link: stackoverflow.com/a/33418582/226895 – expert

3 Answers

75
votes

1) The easiest way to do this is via the aggregation framework. This takes two "$group" commands: the first one groups by distinct values, the second one counts all of the distinct values

pipeline = [ 
    { $group: { _id: "$myIndexedNonUniqueField"}  },
    { $group: { _id: 1, count: { $sum: 1 } } }
];

//
// Run the aggregation command
//
R = db.runCommand( 
    {
    "aggregate": "myCollection" , 
    "pipeline": pipeline
    }
);
printjson(R);

2) If you want to do this with Map/Reduce you can. This is also a two-phase process: in the first phase we build a new collection with a list of every distinct value for the key. In the second we do a count() on the new collection.

var SOURCE = db.myCollection;
var DEST = db.distinct
DEST.drop();


map = function() {
  emit( this.myIndexedNonUniqueField , {count: 1});
}

reduce = function(key, values) {
  var count = 0;

  values.forEach(function(v) {
    count += v['count'];        // count each distinct value for lagniappe
  });

  return {count: count};
};

//
// run map/reduce
//
res = SOURCE.mapReduce( map, reduce, 
    { out: 'distinct', 
     verbose: true
    }
    );

print( "distinct count= " + res.counts.output );
print( "distinct count=", DEST.count() );

Note that you cannot return the result of the map/reduce inline, because that will potentially overrun the 16MB document size limit. You can save the calculation in a collection and then count() the size of the collection, or you can get the number of results from the return value of mapReduce().

38
votes
db.myCollection.aggregate( 
   {$group : {_id : "$myIndexedNonUniqueField"} }, 
   {$group: {_id:1, count: {$sum : 1 }}});

straight to result:

db.myCollection.aggregate( 
   {$group : {_id : "$myIndexedNonUniqueField"} }, 
   {$group: {_id:1, count: {$sum : 1 }}})
   .result[0].count;
2
votes

Following solution worked for me

db.test.distinct('user'); [ "alex", "England", "France", "Australia" ]

db.countries.distinct('country').length 4