In the following query, I want to fetch all verified students and their related meetings. but Trying to fetch all upcoming meetings which means meetings having at least one future date time. My writtten query fetches all verified students but also fetches all the meetings of a student including past one. I want a student to be fetched only if it has any upcoming meetings and fetch only upcoming meetings in returning association. And I want to sort client array of objects according to nearest meeting Any help will be greatly appreciated
let aggregateDataQuery = [
{
$lookup: {
from: 'meetup',
localField: '_id',
foreignField: 'studentId',
as: 'meetup',
},
},
{
$project: {
firstName: 1,
lastName: 1,
email: 1,
meetup: {
_id: 1,
startTime: 1,
},
},
},
];
const [result, err] = await of(
Student.aggregate([
...aggregateDataQuery,
{ $match: {
verified: true,
'meetup.startTime': { '$gte': 2021-09-10T08:41:15.746Z }
}
},
{
$facet: {
data: [
{ $sort: sortBy },
{ $skip: skip },
{ $limit: recordLimit },
],
count: [
{
$count: 'count',
},
],
},
},
])
);
Depending on the scale of the two collections I would suggest starting the query from the meetings collection as you can utilize an index on the meeting time better that way ( imagine there are no future meeting, in the current approach you still match and lookup the entire student collection ).
However the current approach you have is fine and just requires a single minor tweak, we need to filter out the "older" meetings, to do so let's use the join condition lookup syntax.
Now you're new lookup stage will look like:
{
"$lookup": {
"from": "meetup",
"let": {
studentId: "$_id"
},
"pipeline": [
{
$match: {
$expr: {
$and: [
{
$eq: [
"$$studentId",
"$studentId"
],
},
{
$gt: [
"$startTime",
"$$NOW"
],
}
]
}
}
},
{
$project: {
_id: 1,
startTime: 1,
}
}
],
"as": "meetup"
}
}
And the behavior you require will work as intended with the rest of your pipeline.
You could just change the $match query to:
{
$match: {
verified: true,
'meetup.0': {$exists: true}
}
}