Mysql – mongodb equivlant query to this sql group by

mongodbMySQL

I have a collection where most documents have an ISODate column.

I'm wanting to get a basic count by date for that column. In MySQL if I had a datetime column on a table, to get what I'd want I'd do

select date(date_col), count(*) from mytbl group by 1;

It looks like mongos group method might be what I want but I don't seem to be able to get the syntax correct.

Note I'm running version 2.3, so anything specific to 2.4 won't be applicable for me.

Can someone help me with the equivalent mongo translation?

Best Answer

First, 2.3 is a development branch that was turned into 2.4, you should not be using it any longer - hopefully you mean 2.2, which is still supported and developed (though it too will soon be end of life as of writing this answer).

The group method you mention runs server side javascript and is not going to be fast, especially in 2.2 which used the old spidermonkey engine and takes an exclusive javascript lock. Generally I would not recommend using it, especially with a sharded cluster, where it is unsupported.

Instead you should use the aggregation framework, which was added in 2.2, is improved in 2.4 and will be even better in 2.6. The framework includes a $group operator, and and many others that you can use in a "pipeline" to achieve the results you want. If that seems odd, then I recommend the pipeline explanation docs here - for anyone familiar with the Linux/Unix shell, the concept should be very familiar.

To do a group on your field you would do something like this (this would be more specific and easier to be specific if you had actually included a sample document and desired output):

db.mytbl.aggregate(
  [
    { $group : { _id : {date:"$date_col"} , count : { $sum : 1 } } }
  ]
)

You can find some more general examples here:

http://docs.mongodb.org/manual/applications/aggregation/

Related Question