Migration to MongoDB: how to query GROUP BY + WHERE

Question

I have a MYSQL table with people name records and arrival times represented as a number. Think of it as a marathon. I want to know how many people arrived in a certain period of time, who called the same, like this:

SELECT name, COUNT(*) FROM mydb.mytable WHERE Time>=100 AND Time<=1000 GROUP BY name 

And as a result, I get:

 Susan, 1 John, 4 Frederick, 1 Paul, 2 

Now I am switching to MongoDB and using Python to code (so I ask Pymongo to help). I tried to find information about the GROUP BY equivalent (even when I read that NoSQL databases are worse at such an operation than SQL), but since they released a new aggregation API, I could not find a simple example like this using a group method, Map-Reduce method or the new agreggate API.

Any help?

Answer 1

There are examples of this throughout the documentation, Google and this site.

Some links:

• http://api.mongodb.org/python/current/examples/aggregation.html
• http://docs.mongodb.org/manual/reference/aggregation/group/
• http://docs.mongodb.org/manual/reference/aggregation/sum/

And for some code:

 self.db.aggregate( # Lets find our records {"$match":{"Time":{"$gte":100,"$lte":1000}}}, # Now lets group on the name counting how many grouped documents we have {"$group":{"_id":"$name", "sum":{"$sum":1}}} )