[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:
Duplicate
duplicates SERVER-29072 Add support for $lookup into a sub-pi... Closed
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:
https://docs.mongodb.com/master/reference/operator/aggregation/lookup/index.html#join-conditions-and-uncorrelated-sub-queries

Best,
James

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 SERVER-29072

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 SERVER-29072

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):

{$lookup: {
    from: "Invoices",
    match: {"_id": "$invoice"},
    project: {"state": 1},
    as: "invoices"
}}

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 SERVER-22188, too.)

Comment by Jose Antonio Illescas Olmo [ 26/Feb/16 ]

db.order.aggregate([
{
$lookup:

{ from: "products", localField: [ "localField1", "localField2", ... "localFieldN"], foreignField: [ "foreignField1", "foreignField2", ... "foreignField"], as: "productObjects" }

}
])

Comment by Asya Kamsky [ 01/Feb/16 ]

"localField(s)" and "foreignField(s)" must be arrays and match fields by position

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

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