Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
Query Optimization
Description
In many use cases, a user will want to restrict the documents $lookup searches via some additional parameter. For example, they may want:
{
|
$lookup: {
|
from: 'employees',
|
localField: '_id',
|
foreignField: 'manager',
|
as: 'manages'
|
}
|
}
|
But, they may only want to look for employees that have "active: true". Currently, they can do this in two ways:
- Follow the $lookup with a $unwind on 'manages', and then a $match.
- Follow the $lookup with a $project and a $filter on 'manages'.
We will optimize the former case (SERVER-21612) and execute the $match as a query on the foreign collection, but this is not immediately clear from the pipeline.
Another option is, in 3.4, the user could use $graphLookup with a 'maxDepth' of 0 and a 'restrictSearchWithMatch' parameter to achieve the same effect--a poor workaround.
As such, $lookup should allow the user to specify an additional option, 'restrictWithMatch', that is a $match-type filter that is applied to any document from the foreign collection to be returned.
Other features available for $graphLookup that are missing for $lookup:
- localField is a field path only, startWith is an arbitrary expression.