MongoDB Aggregation Queries Basics on MongoLab

December 07, 2012

What is MongoLab?

MongoLab is a hosted MonoDB service that offers one free database to developers wanting to learn the technology. It's a very elegant way to start learning or developing a small application. I started using this earlier in the year and had created a couple of collections on it.

What is the MongoDB Aggregation Framework?

Traditionally in SQL databases you have the concept of a GROUP BY and an aggregation function like average or sum. In MongoDB, aggregation wasn't ever as easy, in order to do any aggregation you'd have to write your own complicated MapReduce jobs. Along comes MongoDB version 2.2, which includes the aggregation framework.

So consider this SQL statement:

SELECT sum(duration) FROM calls GROUP BY disposition

And here it is translated to MongoDB, I've attempted to color code the translations:

   
}
runCommand: {
   aggregate : 'calls',
   pipeline : [
                {
                  $group : {
                    _id : '$disposition',
                    durations: {
                             $sum : '$duration'
                                }
                            }
                 }
               ]
             }
}

Easy enough! There's much more to this of course. Here's a great reference: http://docs.mongodb.org/manual/reference/aggregation/

MongoLabs and Aggregation Framework

So to use this on MongoLabs you'll have to start a new database, old ones are on an older version, according to this helpful article

All free/starter databases created after Nov 30, 2012 will be running 2.2.x will have Aggregation Framework Support

Why Do I care?

Well I had some mongoDB Jaspersoft samples that would bring in an entire un-aggregated dataset into memory and then use the JasperReports library to do aggregations in-memory. This is fine and dandy if you're dealing with tens of thousands of records. When you move into millions of records this becomes a bit harry and I'd rather my database do the work!

So using my example query from above, I was able to create this very cool looking report in iReport: