I'm looking to write a query to aggregate the quantity based on dates.
I have documents that look like this:
{
"_id" : 1234,
"itemNumber" : "item 1",
"date" : ISODate("2021-10-26T21:00:00Z"),
"quantity" : 1,
"__v" : 0
}
And a query like this:
//monogoose
myCollection.aggregate().group({
_id: '$itemNumber',
ninetyDays: {
$sum: {
$and: {
$gte: ["date", dayjs().subtract(90, 'd').toDate()],
$lt: ["date", dayjs().toDate()]
}
}
}
})
In the query above ninetyDays is always 0.
I'm basically looking to get the sum of the quantity given a date range.
Help is much appreciated.
Thank you
You can use $cond to sum 1 or 0 if your condition is match.
Assuming your date expression is correct this should works, but don't forger $ in date.
db.collection.aggregate([
{
"$group": {
"_id": "$itemNumber",
"ninetyDays": {
"$sum": {
"$cond": {
"if": {
"$and": [
$gte: ["$date", dayjs().subtract(90, 'd').toDate()],
$lt: ["$date", dayjs().toDate()]
]
},
"then": 1,
"else": 0
}
}
}
}
}
])
Example here where I've used mongo $$NOW but if your date works is easier to use your code.