Fellows;
Would anyone be so kind as to shed a light on this matter which has been stumping me for a few days now? It should be something simple, but I can't seem to find where the problem lies...
I need to retrieve one year worth of records from a collection based on today's date. Simply put, if today is 2023-02-15, I need records created after 2022-02-15.
This works, of course:
[
{
$match: { _created: { $gte: ISODate("2022-02-15") } }
}
]
The problem is that, when trying to create an aggregation that does not need to constantly be updated, $$NOW does not seem to work and I cannot understand why.
For example, this does not work:
[
{
$match:
{
_created:
{
$gte:
{
$subtract: [ "$$NOW", { $multiply: [1000, 60, 60, 24, 365] } ]
}
}
}
}
]
And this doesn't either:
[
{
$set:
{
startDate: { $subtract: [ "$$NOW", { $multiply: [1000, 60, 60, 24, 365] } ] }
}
},
{
$match:
{
_created: { $gte: ISODate("$startDate") }
}
}
]
I have tried moving things around, parsing the result of $$NOW to get just the date part, converting it to different types... The result is always the same: No errors, but no filtered records either.
And I have the feeling the answer might be something really silly I am just overlooking.
Can someone more experienced lend a helping hand?
P.S.: The aggregation continues after the required records are retrieved, I just did not care to list what happens because the problem relates only to the matching of records. Our Mongod version is 6.0.3, I am working on MongoDB Compass and the idea is to save this aggregation as a query which provides answers to an API call.
You need to use $expr and $gte aggregation operator (do not mistake with $gte Query Operator). In MongoDB 5.0 and newer, you can also use Date Expression Operators like $dateSubtract
{
$match:
{
$expr:
{
$gte:
[ "$_created", { $dateSubtract: { startDate: "$$NOW", unit: "year", amount: 1 } } ]
}
}
}