12.07.2012

MongoDB Aggregation Queries Basics on MongoLab

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:


Where can I learn more about Reporting and Analytics on MongoDB?

First place to look is the Jaspersoft community wiki: http://community.jaspersoft.com/wiki/mongodb
My colleague, Matt Dahlman has some excellent examples on his blog: http://mdahlman.wordpress.com/tag/mongodb/

1 comment:

robin aggarwal said...

hello, i am reading your blog and getting lot of knowledge and solved my problem of Mongodb Technology