I have the following $lookup aggregation stage:
{
$lookup: {
from: "target",
let: {byTargetId: "$foreignTargetId"},
pipeline: [
{
$match: {
$expr: {
$eq: ["$targetId", "$$byTargetId"]
}
}
},
{
$project: {
someTargetProperty: true
}
}
],
as: "targets"
}
}
In my test DB, the stage before this one returns only 5 documents. In this case none of those have the optional property foreignTargetId so I expect byTargetId to be undefined each time, and no document in target collection will match.
The thing is, the aggregation takes 1 second longer if I add this lookup stage (at the end).
If I execute this aggregation on target collection that is identical to the lookup pipeline with a undefined value for byTargetId:
db.getCollection('target').aggregate(
[
{
$match: {
$expr: {
$eq: ["$targetId", undefined]
}
}
},
{
$project: {
someTargetProperty: true
}
}
])
then indeed this takes around 200ms, and 5x200ms = 1s so this makes sense.
Yet if I execute the same aggregation with a null value for byTargetId:
db.getCollection('target').aggregate(
[
{
$match: {
$expr: {
$eq: ["$targetId", null]
}
}
},
{
$project: {
someTargetProperty: true
}
}
])
then this executes in < 1ms.
- What causes this large difference between equality match on
nullandundefined? - How can I skip the $lookup stage if
foreignTargetIdis absent in the input document for this stage?
BTW the reason I'm using a lookup pipeline here is that the documents in target are quite large and I only need someTargetProperty which is also in the index so the lookup can be done completely in memory. I've noticed in other cases that this can be significantly faster than looking up the whole document and projecting later. I won't accept answers that try to get around this issue by not using a $lookup with pipeline.