I am trying to use the MongoDB $lookup with the Uncorrelated Subqueries.
Using MongoDB 3.6.12 (support began on 3.6)
https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries
The following pipeline step is working, however if I swap out the first "userB" with the second, no results are returned.
{
from: 'friendships',
let: { requestuser: ObjectId("5c0a9c37b2365a002367df79"), postuser: ObjectId("5c0820ea17a69b00231627be") },
pipeline: [
{
$match : {
$or : [
{
"userA" : ObjectId("5c0820ea17a69b00231627be"),
"userB" : ObjectId("5c0a9c37b2365a002367df79")
// "userB" : '$$requestuser'
},
{
"userA" : ObjectId("5c0a9c37b2365a002367df79"),
"userB" : ObjectId("5c0820ea17a69b00231627be")
}
],
"accepted" : true
}
},
{
$project: {v: 'true'}
}
],
as: "match"}
Results with hard coded ObjectId:
"match" : [
{
"_id" : ObjectId("5d6171dd319401001fd326bf"),
"v" : "true"
}
]
Results using variable:
"match" : [
]
I feel like ObjectIds need special treatment. All the examples I could find are using simple variables like strings.
To verify the '$$requestUser' contained a value, I tested it on the projection:
"match" : [
{
"_id" : ObjectId("5d6171dd319401001fd326bf"),
"v" : ObjectId("5c0a9c37b2365a002367df79")
}
]
When you use un co-related sub queires, you need to use $expr
to pass a variable.
You can try something like following.
{
$match: {
$expr: {
$or: [
{
$and:[
{
$eq: [ "userA", ObjectId("5c0820ea17a69b00231627be") ]
},
{
$eq: [ "userB", ObjectId("5c0a9c37b2365a002367df79") ]
},
{
$eq: [ "userB", "$$requestuser" ]
}
]
},
{
$and:[
{
$eq: [ "userA", ObjectId("5c0a9c37b2365a002367df79") ]
},
{
$eq: [ "userB", ObjectId("5c0820ea17a69b00231627be") ]
}
]
}
]
},
"accepted": true,
}
}
I have created a sample demo to show how $expr
works inside the lookup
: Sample demo for Uncorrelated Subquery