[SERVER-21913] Allow $lookup to join on multiple keys Created: 15/Dec/15 Updated: 06/Apr/23 Resolved: 23/Nov/17 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 3.2.0 |
| Fix Version/s: | None |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Lee Parayno | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Duplicate | Votes: | 13 |
| Labels: | eng-m, expression | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Assigned Teams: |
Query
|
||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||
| Participants: | |||||||||
| Description |
|
The Aggregation Framework $lookup stage currently only allows single key foreign key equality lookups in a joined collection. This does not allow opportunities where the uniqueness of a joined collection requires multiple keys in the joined collection. One of the earlier syntaxes for $lookup had the following subdocument, which is consistent with find syntax, and would naturally allow multiple key joins: on : { sourceKey1 : $foreignKey1 }or for multiple keys: on : { sourceKey1 : $foreignKey1, sourceKey2 : $foreignKey2 } |
| Comments |
| Comment by Cade Embery [ 28/Nov/17 ] | ||||||
|
Thanks James. Legendary! | ||||||
| Comment by James Wahlin [ 27/Nov/17 ] | ||||||
|
Hi cadeembery, This feature is currently available for testing under the latest MongoDB 3.6 release candidate. It will be available for production use in MongoDB 3.6.0 once released. For more information, please see our documentation for $lookup using pipeline syntax: Best, | ||||||
| Comment by Cade Embery [ 24/Nov/17 ] | ||||||
|
So is this available now? Or in a future release? I am really trying to contain my excitement for the possibility of $lookup $match criteria | ||||||
| Comment by Asya Kamsky [ 23/Nov/17 ] | ||||||
|
Resolved by | ||||||
| Comment by Lee Parayno [ 27/Jun/17 ] | ||||||
|
Asya, that's great news. Thanks. | ||||||
| Comment by Asya Kamsky [ 25/Jun/17 ] | ||||||
|
As announced at MongoDB World, this will be handled by more expressive $lookup currently tracked under | ||||||
| Comment by Dissatisfied Former User [ 13/Jun/16 ] | ||||||
|
There is a more general solution to this: extend the $lookup stage to make use of $match and $project sub-stages. Allowing a match document would resolve this issue and simplify away the special-casing of localField(s) / foreignField(s):
The pseudo-match would map keys from the foreign side to expressions evaluated within the context of the "parent" document. (This would also help resolve | ||||||
| Comment by Jose Antonio Illescas Olmo [ 26/Feb/16 ] | ||||||
|
db.order.aggregate([ } | ||||||
| Comment by Asya Kamsky [ 01/Feb/16 ] | ||||||
Can you explain what you are looking to do exactly, I'm not sure if I'm understanding this particular suggestion. | ||||||
| Comment by Jose Antonio Illescas Olmo [ 30/Jan/16 ] | ||||||
|
IMO, "localField(s)" and "foreignField(s)" must be arrays and match fields by position Another interesting feature: add a lookupOne to lookup single document and map as Object (not array) on results Thanks |