How to group documents by month?

Question

I have a collection of transaction data in mongodb, like this:

[
   {timestamp: ISODate("2015-11-10T11:33:41.075Z"), nominal: 25.121},
   {timestamp: ISODate("2015-11-22T11:33:41.075Z"), nominal: 25.121},
   {timestamp: ISODate("2015-11-23T11:33:41.075Z"), nominal: 26.121},
   {timestamp: ISODate("2015-12-03T11:33:41.075Z"), nominal: 30.121},
]

How can I use mongodb's aggregate to calculate my total transaction each month?

I tried:

db.getCollection('transaction').aggregate([
  { $group: {_id: "$timestamp", total: {$sum: "$nominal"} } }
])

But it failed since I use timestamp instead of month. I don't want to add another field for month to transaction data. I think about a custom made function for $group pipeline that returns month value.

Answer 1

You need a preliminary $project stage where you use the $month operator to return the "month".

 db.transaction.aggregate([
    { "$project": {
        "nominal": 1, 
        "month": { "$month": "$timestamp" }
    }}, 
    { "$group": {
        "_id": "$month", 
        "total": { "$sum": "$nominal" }
    }}
])

Which returns:

{ "_id" : 12, "total" : 30.121 }
{ "_id" : 11, "total" : 76.363 }

Answer 2

In case you want to group per year-month (to avoid months from different years to be grouped together), you can use $dateToString:

// { timestamp: ISODate("2015-11-10T11:33:41.075Z"), nominal: 25.121 }
// { timestamp: ISODate("2015-11-22T11:33:41.075Z"), nominal: 25.121 }
// { timestamp: ISODate("2015-11-23T11:33:41.075Z"), nominal: 26.121 }
// { timestamp: ISODate("2015-12-03T11:33:41.075Z"), nominal: 30.121 }
db.collection.aggregate([
  { $group: {
    _id: { $dateToString: { date: "$timestamp", format: "%Y-%m" } },
    total: { $sum: "$nominal" }
  }}
])
// { _id: "2015-12", total: 30.121 }
// { _id: "2015-11", total: 76.363 }