[SERVER-29376] Rewrite $lookup localField/foreignField stages to execute with pipeline/let Created: 25/May/17  Updated: 06/Dec/22  Resolved: 10/Jun/21

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

Type: Task Priority: Major - P3
Reporter: James Wahlin Assignee: Backlog - Query Optimization
Resolution: Won't Do Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-29073 Allow variable definition within $lookup Closed
Related
related to SERVER-34927 allow localField and foreignField wit... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

With the addition of pipeline/let syntax to $lookup, we have a split in parts of the DocumentSourceLookup implementation to address both forms. We can unify implementation by rewriting $lookup with localField/foreignField syntax to run using a foreign pipeline with match on 'let' variables.



 Comments   
Comment by Hana Pearlman [ 10/Jun/21 ]

We decided to go with the approach in detailed in SERVER-34927 instead.

Comment by Asya Kamsky [ 16/Jan/18 ]

I would strongly advocate against doing this, not only that, but it might even make sense to do the opposite, that is allow "localField" and "foreignField" in expressive lookup syntax to preserve find/equality semantics for existing $lookup that simply need additional stages ($project, $sort, $limit) and not different join conditions.

Comment by James Wahlin [ 28/Sep/17 ]

The problem we would encounter with rewriting $lookup with localField/foreignField syntax to let/pipeline syntax is that dotted path evaluation is not performed in the same way in the matcher and expression languages. The following illustrates this problem. It takes 3 aggregation statements in the following to return the same results as a single match statement.

> db.test.insert({a: {b: 1}})
WriteResult({ "nInserted" : 1 })
> db.test.insert({a: {b: [1]}})
WriteResult({ "nInserted" : 1 })
> db.test.insert({a: [{b: 1}]})
WriteResult({ "nInserted" : 1 })
> db.test.insert({a: [{b: [1]}]})
WriteResult({ "nInserted" : 1 })
>
> db.test.aggregate([{$match: {"a.b": 1 }}])
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fb"), "a" : { "b" : 1 } }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fc"), "a" : { "b" : [ 1 ] } }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fd"), "a" : [ { "b" : 1 } ] }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fe"), "a" : [ { "b" : [ 1 ] } ] }
>
> db.test.aggregate([{$match: {$expr: {$eq: ["$a.b", 1 ]}}}])
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fb"), "a" : { "b" : 1 } }
>
> db.test.aggregate([{$match: {$expr: {$eq: ["$a.b", [1] ]}}}])
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fc"), "a" : { "b" : [ 1 ] } }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fd"), "a" : [ { "b" : 1 } ] }
>
> db.test.aggregate([{$match: {$expr: {$eq: ["$a.b", [[1]] ]}}}])
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fe"), "a" : [ { "b" : [ 1 ] } ] }
>
> db.test.aggregate([{$match: {$expr: {$or: [ {$eq: ["$a.b", 1 ]}, {$eq: ["$a.b", [1] ]}, {$eq: ["$a.b", [[1]] ]}]}}}])
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fb"), "a" : { "b" : 1 } }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fc"), "a" : { "b" : [ 1 ] } }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fd"), "a" : [ { "b" : 1 } ] }
{ "_id" : ObjectId("59cd5851ae86cf2464c9b0fe"), "a" : [ { "b" : [ 1 ] } ] }

A proposal for how to unify will follow.

Comment by David Storch [ 27/Sep/17 ]

james.wahlin, at this point, I think we should bump this out to "3.7 Desired", since it may not land for 3.6. Clearly there is some query language related thinking to do, which I would like us to do sooner rather than later. Perhaps you could produce a small writeup describing how we could unify the semantics of the matcher and the expression language so that the localField/foreignField field is a strict subset of what you can express with $lookup into a subpipeline?

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