I want to filter the dataset to extract documents which were created 7 days ago OR a Month ago OR Documents created at any date.
filter documents based on createdAt field in document.
Dataset:-
[
{
"_id": ObjectId("6257047cffd61ab62864c1ae"),
"type": "A",
"source": "B",
"user": ObjectId("622b55ff0b0af6b049c387d3"),
"createdAt": ISODate("2022-04-17T07:55:00.368Z"),
"updatedAt": ISODate("2022-04-17T07:55:00.368Z"),
},
{
"_id": ObjectId("6257047cffd61ab62864c1ad"),
"type": "B",
"source": "A",
"user": ObjectId("622b55ff0b0af6b049c387d3"),
"createdAt": ISODate("2022-04-23T07:55:00.368Z"),
"updatedAt": ISODate("2022-04-23T07:55:00.368Z"),
},
{
"_id": ObjectId("6257047cffd61ab62864c1ce"),
"type": "A",
"source": "C",
"user": ObjectId("622b55ff0b0af6b049c387d3"),
"createdAt": ISODate("2022-04-17T07:55:00.368Z"),
"updatedAt": ISODate("2022-04-17T07:55:00.368Z"),
},
{
"_id": ObjectId("6257047cffd61ab62864c1cb"),
"type": "A",
"source": "B",
"user": ObjectId("622b56250b0af6b049c387d6"),
"createdAt": ISODate("2022-04-24T07:55:00.368Z"),
"updatedAt": ISODate("2022-04-24T07:55:00.368Z"),
},
{
"_id": ObjectId("6257047cffd61ab62864c1cb"),
"type": "A",
"source": "B",
"user": ObjectId("622b56250b0af6b049c387d6"),
"createdAt": ISODate("2022-03-24T07:55:00.368Z"),
"updatedAt": ISODate("2022-03-24T07:55:00.368Z"),
},
{
"_id": ObjectId("6257047cffd61ab62864c1ce"),
"type": "A",
"source": "C",
"user": ObjectId("622b55ff0b0af6b049c387d3"),
"createdAt": ISODate("2022-03-17T07:55:00.368Z"),
"updatedAt": ISODate("2022-03-17T07:55:00.368Z"),
},
]
MongoDB aggregate query:-
db.collection.aggregate([
{
$addFields: {
paramType: "All",
paramSource: "All",
paramCreatedAt:"All",
}
},
{
$match: {
$and: [
{
user: ObjectId("622b55ff0b0af6b049c387d3")
},
{
$or: [
{
paramType: {
$eq: "All"
}
},
{
$expr: {
$eq: [
"$paramType",
"$type"
],
}
}
]
},
{
$or: [
{
paramSource: {
$eq: "All"
}
},
{
$expr: {
$eq: [
"$paramSource",
"$source"
]
}
}
]
}
]
}
},
{
$setWindowFields: {
output: {
totalCount: {
$count: {}
}
}
}
},
{
$sort: {
createdAt: -1
}
},
{
$skip: 0
},
{
$limit: 6
},
{
"$project": {
"paramSource": false,
"paramType": false,
}
}
])
how to filter to get documents created in the last 7 days or 30 days or any date.
paramCreatedAt will take one of the following values [All dates, 7 days ago, a month ago]
Example:-
- If the
All dates
filter is applied then display all records. - If
7 days
filter is applied display records created from the current date (which can be any day not necessary that it should be sunday) to 7 days back. - If
30 days
filter applied then display records created in last 30 days
2
Answers
Your skeleton is pretty neat and you are actually quite close. For the date filtering, just use
$dateDiff
to return the date difference in days and compare it with the days interval your selected(i.e. 7 days or 30 days) by using$switch
Here is the Mongo playground for your reference.
Here’s an alternate approach using
$facet
.$facet
is very handy because it allows you to "match and group in parallel" and create overlapping buckets of documents. A single pipeline with$group
and$cond
on the aggregation field works well for "if/then/elif/elif/else" constructions where overlaps are not desired and an order of precedence is desired.