I need to get documents from a collection for aggregation starting from the 1st of the current month.
I'm trying this with $dateFromPart, but that isn't working as expected, since it's returning 0 documents, while manually specifying the date new ISODate('2021-10-01') returns the expected result.
I assume it's because I can't use $dateFromParts in the $match stage.
db.messages.aggregate([
{
"$match": {
"timestamp" : {
"$gte" : {
"$dateFromParts" : {
"year" : { "$year" : new ISODate() },
"month" : { "$month" : new ISODate() },
}
}
}
}
}
])
Extra info:
- I Have to use the aggregate, I will be doing some more
$groupoperations later. - I can't use javascript to build dates before the query. This query will be used in Redash, which only accepts a json input, and you can't run any javascript there.
- Obviously I can't use
new ISODate()in redash, I would have to use their built-in$humanTime, but the point remains the same. - The messages collection is big. Over 10 Mil documents, with an index on timestamp. So I need to avoid
COLSCANlike the plague. - Using Mongo 3.6 (I know, time for an upgrade)
- Bonus points if I can do the query from the 1st of the month, three months ago.