Conditional $lookup

Working with Data
#1

Is there a way to do a conditional $lookup?

I have records in a collection that have an optional (null) field. I’d rather not waste processing on the records for which the lookup field is null. Not to mention that, depending on indexing, doing a $lookup on a null field could still be expensive.

$lookup with conditional localField
#2

With this $lookup syntax,

{
   $lookup:
     {
       from: <collection to join>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to execute on the collection to join> ],
       as: <output array field>
     }
}

you can specify condition(s) on the join in the pipeline. In your case, that would be a null check condition (in addition to the join conditions).

#3

Yes, the problem with the solution as you posted is this bug:
https://jira.mongodb.org/browse/SERVER-40362

So when you get to nulls on a lookup join, the query runs SLOWWWWW. Now, one thing I could do is an ifNull, and then change the null to be a value that I am certain won’t be in in the lookup collection… but this seems… jenky. So, was hoping for alternative solution.

#4

You can try using a $match before the $lookup, to exclude the documents with the non-existing field:

{ $match: { someField: { $exists: true } } }

The alternative workaround to use the $ifNull to substitute a null for non-existing field is fine too. Substitute let: { someField : '$someField' } with let: { someField: { $ifNull: ["$someField", null ] } }

In applications it is not unusual to have unusual cases - data or logic. It just happens this case is a program shortcoming (and temporary only). It is not that bad to have a case by covering with an additional condition, if it helps running the program efficiently. Some documentation around this workaround helps for reference and apply the issue fix later on when available.

1 Like
#5

Hello. This conditional lookup does not work for me
MongoDB version: 5
GUI: robo3t-snap

image
image983×726 69.6 KB

updatedFieldsByContractor.updatedValues.cities there is not in the original document. it just appears here because of that lookup. I mean when i run this command: db.getCollection('contractors').find({ _id: ObjectId('htat document') }) to get the same document it had not updatedFieldsByContractor.updatedValues.cities field. what should i do to lookup conventionally?

#6

Hello @Kasir_Barati, you cannot refer the contractors collection document fields directly in the $lookup's pipeline $match stage. Please refer the above linked MongoDB documentation for the $lookup and find correct syntax and usage.