theKindOfMe

January 5, 2012

SQL ‘Group By’ + ‘Order By’ in Mongodb

Filed under: Uncategorized — Tags: , — yasi8h @ 4:50 am

As a newbie to mongodb i was a bit puzzled when i couldn’t find a equivalent to the SQL group by combined with order by in mongodb.

Group

If you just want to go a SQL group by in mongodb you can use the group() function (refer http://www.mongodb.org/display/DOCS/Aggregation#Aggregation-Group). however the results you get from group function cannot be sorted using the sort() function. This is because group function does not return a cursor (on which you can sort(), limit()…etc), but a single document.

You can’t sort the document wich is returned by group but you can sort the document in the client side. This is one way to go about doing a ‘group by + order by’. However if you don’t want to sort things in the client side you can use mapreduce.

MapReduce

This is probably not as simple as you would want it to be. But until mongodb supports a one liner for group by + order by function this is the only way to go about it.

You can refer the MapReduce documentation in the official site to get a better understanding about how it works. But if you just want to do a simple group by + order by equivalent in mongodb you can look at the sample bellow.

Mongodb Data Structure
gplus_profiles: id(objectId), have_user_in_circles_count (int)
SQL
SELECT COUNT(id), `have_user_in_circles_count` from `gplus_profiles`GROUP BY `have_user_in_circles_count` ORDER BY 1 DESC
Mongodb
m = function(){
emit(this.have_user_in_circles_count, { count : 1 });
};
r = function(key, values) {
var total = 0;
for ( var i=0; i<values.length; i++ )
total += values[i].count;
return { count:total };
};
res = db.gplus_profiles.mapReduce(m, r, { out : "myoutputt" } );
db.myoutputt.find().sort({value:-1})

Although we have to use mapreduce for the time being, a later version of mongodb will support group by + order by like functionality natively.


					
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: