I am attempting an left antijoin on these two collections.
I want all users where department is equal to 'IT' that aren't in a meeting that had an endAt time > 175. Either as a creator or receiver. So essentially whoever hasn't been in a meeting in the last xxx time.
Based on below collections: John would be retrieved because he is apart of department IT and has not been a receiver or creator after '175'. Jane has an endAt time after 175 and is in IT so wouldn't be retrieved Bill is apart of finance so even though he hasn't been it doesn't matter Bob has an endAt time after 175 and is in IT so wouldn't be retrieved Mary is in IT and has not been in any meetings so she is retrieved.
Users Collection:
[
{
_id: ObjectId("1"),
name: "john",
department: 'IT'
},
{
_id: ObjectId("2"),
name: "jane",
department: 'IT'
},
{
_id: ObjectId("3"),
name: "bill",
department: 'finance'
},
{
_id: ObjectId("4"),
name: "Bob",
department: 'IT'
},
{
_id: ObjectId("5"),
name: "Mary",
department: 'IT'
}
]
Meetings Collection:
[
{
_id: ObjectId("a"),
endedAt: 100,
creator_id: ObjectId("1"),
receiver_id: ObjectId("2")
},
{
_id: ObjectId("b"),
endedAt: 150,
creator_id: ObjectId("1"),
receiver_id: ObjectId("3")
},
{
_id: ObjectId("c"),
endedAt: 200,
creator_id: ObjectId("4"),
receiver_id: ObjectId("2")
},
{
_id: ObjectId("d"),
endedAt: 250,
creator_id: ObjectId("2"),
receiver_id:
}
]
Output:
[
{
_id: ObjectId("1"),
name: "john",
department: 'IT'
},
{
_id: ObjectId("5"),
name: "Mary",
department: 'IT'
}
]
My approach:
db.users.aggregate([
{
$match:
{
type: 'IT'
}
},
{
$lookup:
{
from: "meetings",
let:
{
userid: "$_id",
},
pipeline: [
{ $match:
{ $expr:
{
$and:[
{
$or: [
{ $eq: ["$receiver_id", "$$userid"] },
{ $eq: ["$creator_id", "$$userid"] },
]
},
{ $gt: ["$endAt", 175] }
]
}
}
}
],
as: "result"
}
},
{
$unwind:
{
path: "$result",
preserveNullAndEmptyArrays: true
}
},
{
$match:
{
result: {$exists:false}
}
}
])
Query
db.users.aggregate([
{
"$match": {
"department": {
"$eq": "IT"
}
}
},
{
"$lookup": {
"from": "meetings",
"let": {
"userid": "$_id"
},
"pipeline": [
{
"$match": {
"$expr": {
"$and": [
{
"$gt": [
"$endedAt",
175
]
},
{
"$or": [
{
"$eq": [
"$$userid",
"$creator_id"
]
},
{
"$eq": [
"$$userid",
"$receiver_id"
]
}
]
}
]
}
}
},
{
"$project": {
"_id": 1
}
}
],
"as": "meetings"
}
},
{
"$match": {
"$expr": {
"$eq": [
"$meetings",
[]
]
}
}
},
{
"$unset": [
"meetings"
]
}
])