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 datesfilter is applied then display all records. - If
7 daysfilter 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 daysfilter applied then display records created in last 30 days
CodePudding user response：
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
db.collection.aggregate([
{
$addFields: {
paramType: "All",
paramSource: "All",
paramCreatedAt: "All dates"// [All dates, 7 days ago, a month ago]
}
},
{
$match: {
$and: [
{
user: ObjectId("622b55ff0b0af6b049c387d3")
},
{
$or: [
{
paramType: {
$eq: "All"
}
},
{
$expr: {
$eq: [
"$paramType",
"$type"
],
}
}
]
},
{
$or: [
{
paramSource: {
$eq: "All"
}
},
{
$expr: {
$eq: [
"$paramSource",
"$source"
]
}
}
]
},
{
$or: [
{
paramCreatedAt: {
$eq: "All dates"
}
},
{
$expr: {
$and: [
{
"$in": [
"$paramCreatedAt",
[
"7 days ago",
"a month ago"
]
]
},
{
$lte: [
{
"$dateDiff": {
"startDate": "$createdAt",
"endDate": "$$NOW",
"unit": "day"
}
},
{
"$switch": {
"branches": [
{
"case": {
$eq: [
"$paramCreatedAt",
"7 days ago"
]
},
"then": 7
},
{
"case": {
$eq: [
"$paramCreatedAt",
"a month ago"
]
},
"then": 30
}
]
}
}
]
}
]
}
}
]
}
]
}
},
{
$setWindowFields: {
output: {
totalCount: {
$count: {}
}
}
}
},
{
$sort: {
createdAt: -1
}
},
{
$skip: 0
},
{
$limit: 6
},
{
"$project": {
"paramSource": false,
"paramType": false,
}
}
])
Here is the Mongo playground for your reference.
CodePudding user response：
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.
db.foo.aggregate([
// Initial filter(s):
{$match: {user: ObjectId("622b55ff0b0af6b049c387d3")}},
// Create a single version of "now" from the perspective of the
// CLIENT to use in queries to follow.
// To create such a target date from the perspective of the SERVER,
// use {$addFields: {DD: '$$NOW'}}
// Probably overkill but OK.
{$addFields: {DD: new ISODate()}},
{$facet: {
"all": [ ], // not exciting! :-)
"exactly_7_days_ago": [
{$match: {$expr:
{$eq: [7, {$floor: {$divide:[{$subtract:['$DD', '$createdAt'] }, 1000 * 60 * 60 * 24]}} ]}
}}
],
"everything_from_last_month": [
{$match: {$expr:
{$eq: [1, {$subtract:[{$month: '$DD'}, {$month: '$createdAt'} ]} ]}
}}
],
"only_one_day_from_last_month": [
{$match: {$expr:
{$and: [
{$eq: [1, {$subtract:[{$month: '$DD'}, {$month: '$createdAt'}]} ]},
{$eq: [0, {$subtract:[{$dayOfMonth: '$DD'}, {$dayOfMonth: '$createdAt'} ]} ]}
]}
}}
],
}}
]);