[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:
But, they may only want to look for employees that have "active: true". Currently, they can do this in two ways:
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:
|
| 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 ] |
The problem is that this cannot be used when preserveNullAndEmptyArrays is set to true, and therefore causes a major performance issue. |