[SERVER-25051] $lookup should support an "restrictWithMatch" option Created: 13/Jul/16  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Benjamin Murphy Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 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.


 Comments   
Comment by David Storch [ 21/May/19 ]

I think we intended to flag this ticket for triage after getting input from Asya, but never did. I'm doing that now.

Comment by Asya Kamsky [ 20/Mar/19 ]

Consider for quick win nomination

Comment by Kanagavelu Sugumar [ 01/Mar/17 ]

This will save memory and process time in case of time bucketed analytics, where time range in one collection and match criteria is on another collection. http://stackoverflow.com/questions/42516573/mongodb-join-or-lookup-via-nested-sub-document-field

Comment by Asya Kamsky [ 17/Feb/17 ]

Follow the $lookup with a $unwind on 'manages', and then a $match.

The problem is that this cannot be used when preserveNullAndEmptyArrays is set to true, and therefore causes a major performance issue.

Generated at Thu Feb 08 04:08:08 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.