I want to fetch records that exists in provided dates array.
This provided array of dates will fetch records between start & end date.
Example:
I have this schema:
[
{
"_id": "1",
"course": "Maths",
"startDate": "2022-06-07",
"endDate": "2022-06-12"
},
{
"_id": "2",
"course": "Chemistry",
"startDate": "2022-06-06",
"endDate": "2022-06-09"
},
{
"_id": "3",
"course": "Physics",
"startDate": "2022-06-08",
"endDate": "2022-06-10"
},
{
"_id": "4",
"course": "Computer Science",
"startDate": "2022-06-10",
"endDate": "2022-06-18"
},
{
"_id": "5",
"course": "Computer Science",
"startDate": "2022-06-10",
"endDate": "2022-06-13"
},
{
"_id": "6",
"course": "Biology",
"startDate": "2022-06-08",
"endDate": "2022-06-10"
}
]
Provided array:
["2022-06-06", "2022-06-17"]
The expected response is:
[
{
"_id": "2",
"course": "Chemistry",
"startDate": "2022-06-06",
"endDate": "2022-06-09"
},
{
"_id": "4",
"course": "Computer Science",
"startDate": "2022-06-10",
"endDate": "2022-06-18"
}
]
Can anyone help me with the query? Thanks
You can use an aggregation pipeline with $filter:
matchingDates field that counts the items in the dates array that matches the condition.$match only documents that have such items.db.collection.aggregate([
{$addFields: {dates: ["2022-06-06", "2022-06-17"]}},
{
$set: {
matchingDates: {
$size: {
$filter: {
input: "$dates",
as: "item",
cond: {
$and: [
{$gte: [{$toDate: "$$item"}, {$toDate: "$startDate"}]},
{$lte: [{$toDate: "$$item"}, {$toDate: "$endDate"}]}
]
}
}
}
}
}
},
{$match: {matchingDates: {$gt: 0}}},
{$unset: ["dates", "matchingDates"]}
])