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.
- this one how to do distinct and group in mongodb? (not accepted, answer author / OP didn't test it)
- this one MongoDB group by Functionalities (seems similar to Second Attempt)
- this one http://blog.emmettshear.com/post/2010/02/12/Counting-Uniques-With-MongoDB
- this one https://groups.google.com/forum/?fromgroups#!topic/mongodb-user/trDn3jJjqtE
- this one http://cookbook.mongodb.org/patterns/unique_items_map_reduce/
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)