Execute math on aggregate $group

[cmendes0101]

I'm trying to perform a $group similar to SQL and thats working fine, but I have records that are in seconds and I need to convert to minutes then round up. Is it possible to perform math function during a $group $sum?

$length would be in seconds so need to do something similar to CEIL($length / 60).

The $group section of the Aggregate command:

$group : { _id: "$cust_id", count: {$sum: 1},minutes: { $sum: $length}}

[Andrew Emil]

Hello, it is actually possible to solve this problem using commands within the aggregation. Specifically you could use the $project (documentation: http://docs.mongodb.org/manual/reference/aggregation/project/#_S_project ) command in association with the $divide (documentation: http://docs.mongodb.org/manual/reference/aggregation/#_S_divide ) command to divide all of the desired fields by 60, converting from minutes to hours.

Let me show you some test code I whipped up:

First to insert documents in the database I did a quick for loop in the javascript shell:

for(var i = 0; i < 1000; i++) {

    db.test.insert({'seconds': i * 60})

}

db.test.find() now returns

{ "_id" : ObjectId("5058f51608768f08f82dc78f"), "seconds" : 0 }

{ "_id" : ObjectId("5058f51608768f08f82dc790"), "seconds" : 60 }

{ "_id" : ObjectId("5058f51608768f08f82dc791"), "seconds" : 120 }

....

Now, I construct the aggregation query:

db.test.aggregate( { $project: {

    'seconds': 1,

    'minutes' : { $divide: ['$seconds', 60] } } } )

which returns an array that looks like:

[ { "_id" : ObjectId("5058f51608768f08f82dc78f"), "seconds" : 0, 'minutes': 0 }

{ "_id" : ObjectId("5058f51608768f08f82dc790"), "seconds" : 60, 'minutes': 1 }

{ "_id" : ObjectId("5058f51608768f08f82dc791"), "seconds" : 120, 'minutes': 2 }

....]

However, this method will not necessarily return integer values.  For example, if it had been 90 seconds, you would see minutes as 1.5, which might not be desirable.  To avoid this we can do a more complicated query.  This query will simply truncate the decimal value inside of the aggregation framework:

db.test.aggregate( { $project: { 

    'seconds': 1,

    'minutes' : { 

        $subtract : [{ 

            $divide: ['$seconds', 60] }, { 

            $divide: [{ 

                $mod: ['$seconds', 60] }, 

                60 ] 

            } 

        ] 

    } 

} } )

This is essentially setting minutes to:

seconds/60 - ( (seconds mod 60) / 60 )

which is the truncated value of seconds/60 (NOTE: not the rounded value).  This gets quite complex however and it might just be easier to handle these finer matters outside of your mongo query. 

Hopefully this helps you with your problem, feel free to respond with any questions or comments.