Tuesday 9 May 2017

Mongo / Mongoose - Aggregating by Date

I have a mongo/mongoose schema which when queried retruns documents such as

{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -33.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-04-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -61.3, "name" : "Amazon", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "Tesco", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -26.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -63.3, "name" : "Sky", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }

I would like to write a query what provides the yearly, monthly and weekly spend for each of the vendors ("name" : "Amazon"), so for example for vendor RINGGO:

  • There are three spends in 2017 33.3+26.3+3.3 so total yearly spend would be 59.9
  • There are two spends in the month 2017-03 with a sum of 26.3+3.3 so the monthly total would be 26.6
  • Each of the spends are in different weeks so the weekly totals would be (for example) wk12 26.3, wk13 3.3, wk 15 33.3

I can write a query such as

db.statements.aggregate(
   [        
       { $group : { _id : "$name", amount: { $push: "$amount" } } }
   ]
)

which will aggregate all spends (amount) by vendor name, but I'm not sure how to break this down by year, month, week as described above.



via Stuart Brown

No comments:

Post a Comment