[SERVER-29072] Add support for $lookup into a sub-pipeline Created: 04/May/17  Updated: 30/Oct/23  Resolved: 19/May/17

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

Type: Task Priority: Major - P3
Reporter: James Wahlin Assignee: James Wahlin
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-10298 Docs for SERVER-29072: Add support fo... Closed
Duplicate
is duplicated by SERVER-21913 Allow $lookup to join on multiple keys Closed
is duplicated by SERVER-27517 $lookup into a pipeline Closed
is duplicated by SERVER-31747 Allow projections inside lookup stage... Closed
is duplicated by SERVER-22188 allow $lookup condition that's not eq... Closed
is duplicated by SERVER-27372 Add Comparison Operators in $lookup Closed
Related
related to SERVER-29371 Add support for a $lookup stage withi... Closed
Backwards Compatibility: Major Change
Sprint: Query 2017-05-29
Participants:

 Description   

Extend command syntax to allow for "pipeline" field as an alternative to localField/foreignField



 Comments   
Comment by henry clifford [ 01/Dec/17 ]

thanks James!

Comment by James Wahlin [ 01/Dec/17 ]

Hi henry@live.xyz,

As of our latest 3.6 release candidate, an equality join using $expr will not make use of indexes, which is likely the cause of the performance differential you see. We are working on adding index use for equality join under SERVER-31760. Please watch that ticket for updates.

Best,
James

Comment by henry clifford [ 01/Dec/17 ]

is there a way to get similar performance when using the pipeline syntax to the more traditional local/foreignField?

there is a very large perf difference between

db.authors.aggregate([
  {
    $lookup: {
      as: "books",
      from: "books",
      localField: "_id",
      foreignField: "authorId"
    }
  }
])

and the pipeline syntax (using $expr as recommended by the docs)

db.authors.aggregate([
  {
    $lookup: {
      as: "books",
      from: "books",
      let: { 
        authorId: "$_id"
      },
      pipeline: [
        $match: {
          $expr: {
            $eq: [ "$authorId",  "$$authorId" ]
          }
        }
      ]
    }
  }
])

this example is intentionally simplified

thanks!

Comment by Githook User [ 19/May/17 ]

Author:

{u'username': u'jameswahlin', u'name': u'James Wahlin', u'email': u'james.wahlin@10gen.com'}

Message: SERVER-29072 Add support for $lookup into a sub-pipeline
Branch: master
https://github.com/mongodb/mongo/commit/7cc042a4f8d21354b36d44f6b3642d2795ecb9ee

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