Mongo / Mongoose - aggregation by date
I have a mongo / mongoose schema that, when requested, fetches documents like
{ "_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 that foresees annual, monthly and weekly costs for each of the vendors ( "name" : "Amazon"
), for example, for the RINGGO vendor:
- There are three spending in 2017, 33.3 + 26.3 + 3.3, so the total annual amount is 59.9.
- There are two spending in the month 2017-03 with a total of 26.3 + 3.3, so the monthly amount is 26.6.
- Each of the costs 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 like
db.statements.aggregate(
[
{ $group : { _id : "$name", amount: { $push: "$amount" } } }
]
)
which will aggregate all expenses ( amount
) by vendor name, but I'm not sure how to break it down by year, month, week as described above.
Edit in response to comment I'm not entirely sure if the form can have a result, but ideally it would look something like this:
I need the year, month, week, etc., so that queries can be controlled using url (eg, domain.com/vendorname/2017
, domain.com/vendorname/2017/3
, domain.com/vendorname/2017/3/12
)
I also like both the individual costs and the total costs for each year / month / week as I would like to print them on a page.
{ "_id" :
{ "year" : 2017,
"month" : 3,
"week" : 12 },
"name": "RINGGO", //vendor name
"YearlySpends":[ 33.3, 26.3, 3.3]
"totalYearlylyAmount" : [ 59.9]
"MonthlySpends":[ 26.3, 3.3]
"totalMonthlyAmount" : [ 26.6]
"WeeklylySpends":[ 3.3]
"totalWeeklylyAmount" : [3.3]
}
source to share
A good approach would be to split the aggregate piping into several stages in order to calculate the aggregates with each group, that is, annual, monthly and weekly aggregates.
I made a small attempt at creating the pipeline mentioned but not sure what what you are after but might give you some leads to a solution but better yet. Perhaps someone else can provide a better answer.
Consider the following untested pipeline:
db.statements.aggregate([
{
"$group": {
"_id": {
"name": "$name",
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"week": { "$week": "$date" }
},
"total": { "$sum": "$amount" }
}
},
{
"$group": {
"_id": {
"name": "$_id.name",
"year": "$_id.year"
},
"YearlySpends": { "$push": "$total" },
"totalYearlyAmount": { "$sum": "$total" },
"data": { "$push": "$$ROOT" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"month": "$data._id.month"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$push": "$data.total" },
"totalMonthlyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"week": "$data._id.week"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$push": "$data.total" },
"totalWeeklyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": "$data._id",
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$first": "$WeeklySpends" },
"totalWeeklyAmount": { "$first": "$totalWeeklyAmount" }
}
}
])
Output result
/* 1 */
{
"_id" : {
"name" : "Tesco",
"year" : 2017,
"month" : 3,
"week" : 11
},
"YearlySpends" : [
-3.3
],
"totalYearlyAmount" : -3.3,
"MonthlySpends" : [
-3.3
],
"totalMonthlyAmount" : -3.3,
"WeeklySpends" : [
-3.3
],
"totalWeeklyAmount" : -3.3
}
/* 2 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 4,
"week" : 17
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-33.3
],
"totalMonthlyAmount" : -33.3,
"WeeklySpends" : [
-33.3
],
"totalWeeklyAmount" : -33.3
}
/* 3 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 12
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-3.3,
-26.3
],
"totalMonthlyAmount" : -29.6,
"WeeklySpends" : [
-3.3
],
"totalWeeklyAmount" : -3.3
}
/* 4 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 11
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-3.3,
-26.3
],
"totalMonthlyAmount" : -29.6,
"WeeklySpends" : [
-26.3
],
"totalWeeklyAmount" : -26.3
}
/* 5 */
{
"_id" : {
"name" : "Sky",
"year" : 2017,
"month" : 3,
"week" : 9
},
"YearlySpends" : [
-63.3
],
"totalYearlyAmount" : -63.3,
"MonthlySpends" : [
-63.3
],
"totalMonthlyAmount" : -63.3,
"WeeklySpends" : [
-63.3
],
"totalWeeklyAmount" : -63.3
}
/* 6 */
{
"_id" : {
"name" : "Amazon",
"year" : 2017,
"month" : 3,
"week" : 12
},
"YearlySpends" : [
-61.3
],
"totalYearlyAmount" : -61.3,
"MonthlySpends" : [
-61.3
],
"totalMonthlyAmount" : -61.3,
"WeeklySpends" : [
-61.3
],
"totalWeeklyAmount" : -61.3
}
UPDATE
If you want to include filters in an aggregated operation, I suggest you use the query as the first stage of the pipeline. However, if there is an initial step , then the previous steps would be slightly modified, since you will be aggregating the filtered results, very different from aggregating all documents as a whole, and then filtering on the results. $match
$match
If you want to use the-first-then-aggregate route filter, try running an aggregate operation which uses as the first step that filters documents by the provider, and then the previous step to further filter documents in the month part of the date field, and then the rest will be steps: $match
$redact
$group
Statements.aggregate([
{ "$match": { "name": req.params.vendor } },
{
"$redact": {
"$cond": [
{ "$eq": [{ "$month": "$date" }, parseInt(req.params.month) ]},
"$$KEEP",
"$$PRUNE"
]
}
},
.....
/*
add the remaining pipeline steps after
*/
], function(err, data){
if (err) throw err;
console.log(data);
})
If you want to take a first-to-filter route, then the filter will be after the last pipeline, which will give a grouped result, but will be applied in different fields, since the documents located above that part of the stream will be different from the original scheme.
This route doesn't work as you start an aggregated operation on all documents in the collection and then filter:
Statements.aggregate([
.....
/*
place the initial pipeline steps from
the original query above here
*/
.....
{
"$match": {
"_id.name": req.params.vendor,
"_id.month": parseInt(req.params.month)
}
}
], function(err, data){
if (err) throw err;
console.log(data);
})
For multiple date filter parameters, the operator will be $redact
{
"$redact": {
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, parseInt(req.params.year) ]},
{ "$eq": [{ "$month": "$date" }, parseInt(req.params.month) ]},
{ "$eq": [{ "$week": "$date" }, parseInt(req.params.week) ]}
]
},
"$$KEEP",
"$$PRUNE"
]
}
}
source to share