What is the best way to find the most frequently occurring value in MongoDB?

Question

I am looking for the equivalent of such an SQL query.

SELECT field, count(*) as counter from table order by counter DESC

What is the best way to achieve this?

thanks

Answer 1

Use Map-Reduce. Match each document by emitting a key and a value of 1, and then combine them using a simple reduction operation. See http://www.mongodb.org/display/DOCS/MapReduce

Answer 2

I would handle aggregation requests, tracking the corresponding counts separately, i.e. in your own collection. That way, you can simply request the “most common” collection. Disadvantage: you need to make another record whenever the data changes.

Of course, you can also update this collection from time to time using Map / Reduce. It depends on how accurate the information is and how often it changes.

Make sure, however, that you do not often invoke the Map / Reduce operation: it is not intended to be used interactively (i.e. not in every view on the page), but rather in offline mode, which updates the counts every hour or so. Therefore, if your counts change very quickly, use a collection of counters.