GRAILS / JAVA MONGODB : Aggregation / Grouping

Running a aggregation or grouping in mongo is not straight forward and the only way is map / reduce. I tried both grails plugins Morphia and mongoDB GORM and after googling my ass off I found that it is not possible to do grouping even with criteria builder offered by the GORM plugin :(. Which means groupProperty('col1') wont work :'( :'(

Now, coming to the implementation part. Let's assume the below SQL query which we want to run in mongo

Select col1,count(*) from table1 where col1 = 'blahblah' group by col1

So, now we need to run this type of query in any of mongo collection using a grails service. I am using grails morphia plugin. You can find the plugin here.

The code is divided into below parts.

First inject the bean into your service class

def mongo

Now, get the instance of the collection you want to do grouping on

DBCollection myColl = mongo.db.getCollection("mycoll")

We will define some conditions basically MAP

DBObject condition = new BasicDBObject();
condition.put("lastUpdated",date)
//In case you want multiple conditions on a single column
condition.put("lastUpdated",new BasicDBObject('$gt',fromDate).append('$lt',toDate))

Lets define our grouping column, basically known as key

DBObject keys = new BasicDBObject();
keys.put('col1',true);
//If you want to add more columns for grouping
keys.put('col2',true);

Setting the initial value of count to zeeroooo

DBObject initial = new BasicDBOject()
initial.put("count",0)

Finally the reduce function where we will do the counting

String reduce = "function(duration,out){ out.count++ }"

That's all.. now we are set to get the results. The return value will be a BSONList

DBObject results = myColl.group(keys,condition,initial,reduce)

Finally putting it all together...

Class MyMongoService {
         
        def mongo

        def myMongoGrouping(params) {
             DBCollection myColl = mongo.db.getCollection("myColl")
             DBObject condition = new BasicDBObject()
             DBObject initial = new BasicDBObject()
             DBObject keys = new BasicDBObject()

             //conditions
             condition.put("lastUpdated",new BasicDBObject('$gt',fromDate).append('$lt',toDate))

             //grouping column
             keys.put('col1',true)
             
             //setting counter to 0
             initial.put("count",0)

             //writing the reduce function
             String reduce = "function(duration,out){ out.count++; }"

             //Here goes our results, a BSONList
             DBObject results = myColl.group(keys,condition,initial,reduce)

        }
}

In case you are using the GORM plugin for GRAILS then you can get the connection with this line

def db = mongo.getDB("myDb")

Rest everything should remain the same. Not tried tough with the GORM plugin.

HAPPY CODING :)
-Lalit