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'
}
]
I've tried multiple aggregations and can't seem to get to the expected result.
CodePudding user response：
aggregate
db.users.aggregate([
{
"$match": {
department: "IT"
}
},
{
"$lookup": {
"from": "meeting",
"localField": "_id",
"foreignField": "creator_id",
"as": "meeting_creator"
}
},
{
"$lookup": {
"from": "meeting",
"localField": "_id",
"foreignField": "receiver_id",
"as": "meeting_receiver"
}
},
{
"$match": {
"$and": [
{
"meeting_creator.endedAt": {
"$not": {
"$gt": 175
}
}
},
{
"meeting_receiver.endedAt": {
"$not": {
"$gt": 175
}
}
}
]
}
},
{
"$project": {
_id: 1,
name: 1,
department: 1
}
}
])
data
db={
"users": [
{
_id: "1",
name: "john",
department: "IT"
},
{
_id: "2",
name: "jane",
department: "IT"
},
{
_id: "3",
name: "bill",
department: "finance"
},
{
_id: "4",
name: "Bob",
department: "IT"
},
{
_id: "5",
name: "Mary",
department: "IT"
}
],
"meeting": [
{
_id: "a",
endedAt: 100,
creator_id: "1",
receiver_id: "2"
},
{
_id: "b",
endedAt: 150,
creator_id: "1",
receiver_id: "3"
},
{
_id: "c",
endedAt: 200,
creator_id: "4",
receiver_id: "2"
},
{
_id: "d",
endedAt: 250,
creator_id: "2",
receiver_id: ""
}
]
}
result
[
{
"_id": "1",
"department": "IT",
"name": "john"
},
{
"_id": "5",
"department": "IT",
"name": "Mary"
}
]
CodePudding user response：
Query
- match "IT"
- join if >175 AND (userid in any of the 2 (creator/receiver))
*its lookup pipeline, because multiple join creteria - reject those that are joined
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"
]
}
])
CodePudding user response：
This is the solution I came up with that ended up working, does anyone have any details what would be the most efficient?
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: ["$startAt", 175] }
]
}
}
}
],
as: "result"
}
},
{
$unwind:
{
path: "$result",
preserveNullAndEmptyArrays: true
}
},
{
$match:
{
result: {$exists:false}
}
}
])