Aggregate functions with MongoDB/Mongoid and calculated fields

Question

I'm migrating an existing Rails app to use MongoDB (with Mongoid), and I'm having some trouble figuring out how to do aggregations like you can do with MySQL.

Previously I had something like SELECT DATE(created_at) AS day, SUM(amount) AS amount GROUP BY day, Which would return a collection that you can loop through in the template like this:

:day => '2011-03-01', :amount => 55.00 
:day => '2011-03-02', :amount => 45.00
etc...

Does anyone know how to do that in Mongoid? The model is pretty straightforward:

class Conversion
  include Mongoid::Document
  include Mongoid::Timestamps

  field :amount,      :type => Float, :default => 0.0
  ...
  # created_at generated automatically, standard Rails...
end

Thanks!

-Avishai

score 5 · Accepted Answer · answered Mar 25 '11 at 03:40

Unfortunately, it's a bit harder than you would think.

My understanding is that Mongoid itself does not support grouping and addition in one query, to do so - you'll need to do a map/reduce directly with the MongoDB driver. I ran this example with a similar data structure to what you have, but there may be a couple of errors during the translation, if you have any trouble, let me know and i'll try and amend it.

First you need to define your map and reduce functions. These have to be stored as JavaScript strings.

map = "function(){ emit(new Date(this.created_at.getYear(), this.created_at.getMonth(), this.created_at.getDate()), {amount: this.amount}); };"
reduce = "function(key, values){ var sum = 0; values.forEach(function(doc){ sum += doc.amount; }); return {amount: sum};};"

Then you call map_reduce directly on the collection, in this case:

Conversion.collection.map_reduce(map, reduce).find.to_a

Which will return an array of hashes like the following:

[{"_id"=>Sat Dec 13 13:00:00 UTC 0110, "value"=>{"amount"=>55.0}}, {"_id"=>Sat Jan 17 13:00:00 UTC 0111, "value"=>{"amount"=>45.0}}]

That actually works! A bit more of a pain to use than standard ActiveRecord style queries because you have to get the results as `result['_id']['value']['amount']`, but pretty cool :-) — Avishai, Mar 27 '11 at 12:45

Jonathan · Answer 2 · 2012-04-30T18:51:46.513

Mongoid also has a handful of shortcuts for simple calculations:

You can add the following to any scope:

.max(:age)
.min(:quantity)
.sum(:total)

Note this will not be as fast as m/r for it loops through the cursor in the application vs in mongo. But if your result set is not that large, its a good option

Here is the source: https://github.com/mongoid/mongoid/blob/2.4.0-stable/lib/mongoid/contexts/enumerable.rb

Also stay tuned for the forthcoming aggregation framework: http://www.mongodb.org/display/DOCS/Aggregation+Framework

Aggregate functions with MongoDB/Mongoid and calculated fields

2 Answers2