[SERVER-19095] $lookup Created: 23/Jun/15  Updated: 28/Jun/16  Resolved: 01/Oct/15

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

Type: New Feature Priority: Major - P3
Reporter: Ian Whalen (Inactive) Assignee: Charlie Swanson
Resolution: Done Votes: 9
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-12685 Expand $unwind behavior to include em... Closed
Duplicate
is duplicated by SERVER-745 Embedded Document Expansion (Pseudo-J... Closed
is duplicated by SERVER-5048 $project to have ability to get data ... Closed
is duplicated by SERVER-11682 Aggregate on multiple collections Closed
is duplicated by SERVER-11865 A $findOne operator for $let,$map in ... Closed
is duplicated by SERVER-19270 Allow adding additional source collec... Closed
is duplicated by SERVER-432 server side expansion of DBRefs Closed
is duplicated by SERVER-970 MapReduce:add ability to process many... Closed
is duplicated by SERVER-19476 Referenced Values Closed
is duplicated by SERVER-1632 improve find with using DBRef to supp... Closed
is duplicated by SERVER-21621 Piped query Closed
Related
related to CSHARP-1374 $lookup Closed
related to JAVA-1936 Aggregation builder support for $look... Closed
is related to DRIVERS-234 Aggregation Builder Support for 3.2 Closed
is related to SERVER-20504 Name aggregation operator for lookup:... Closed
Backwards Compatibility: Fully Compatible
Sprint: Quint Iteration 7, QuInt 8 08/28/15, Quint 9 09/18/15, QuInt A (10/12/15)
Participants:

 Description   

Summary

Add a $lookUp operator to the aggregation pipeline to pull data from one collection into a pipeline executed on a different collection.

Goals

  • Aggregation stage to do left outer join
  • Works across any shard/collection boundary
    • left-hand side sharded, right-hand side unsharded

Non-Goals

  • Enforced collocation of “related” chunks.
  • More parallelization for agg
  • Small collections that co-exist on all shards (aka, “enum” collections)

Examples

Forthcoming



 Comments   
Comment by Ramon Fernandez Marina [ 28/Jun/16 ]

mgoetzke, please take a look at the $graphLookup operator in SERVER-23725 to see if it meets your needs.

Thanks,
Ramón.

Comment by Matthias Götzke [ 28/Jun/16 ]

good start .. is there a plan to make this join recursively .. a->b->c->d ... ?

Comment by Ramon Fernandez Marina [ 04/Nov/15 ]

StealthyDev, MongoDB 3.2.0-rc2 release candidate has been released and its available for download. Thanks for your patience.

Comment by Ramon Fernandez Marina [ 03/Nov/15 ]

StealthyDev, we're working on 3.2.0-rc2 right now, it should be available in the coming days. An announcement will be posted in the mongodb-announce, mongodb-dev and mongodb-user groups, as well as the MongoDB blog when 3.2.0-rc2 is available for download.

Comment by Jason Singleton [ 03/Nov/15 ]

@StealthyDev - You can try the latest nightly build from the main downloads page. https://www.mongodb.org/downloads#development Not sure if it's made the build yet but its the latest you can get other than building from source.

Comment by StealthyDev [X] [ 03/Nov/15 ]

Sorry I was not sure where to ask this question.
Just wondering when is the release date for 3.2.0-rc2? Is there a daily build i can download to try this $lookup feature?

[edit] Another question: is this feature not available in the free version of MongoDB?

Thank you

Comment by Githook User [ 30/Oct/15 ]

Author:

{u'username': u'cswanson310', u'name': u'Charlie Swanson', u'email': u'cswanson310@gmail.com'}

Message: SERVER-19095 Move $lookup to community edition
Branch: master
https://github.com/mongodb/mongo/commit/2cc2d7da2be074f5a7d75c4c435a72b0303f7eaf

Comment by Githook User [ 30/Oct/15 ]

Author:

{u'username': u'cswanson310', u'name': u'Charlie Swanson', u'email': u'cswanson310@gmail.com'}

Message: SERVER-19095 Move $lookup to community edition
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/9c7bd6e5fbb5422503063e72419755fd59dc7e38

Comment by John A. De Goes [ 29/Oct/15 ]

Hurray! Thanks to all the folks at MongoDB who made this happen, and to all the users whose voices were ultimately heard!

Comment by Ron Avnur [ 29/Oct/15 ]

All,

We are making a change that is detailed in https://www.mongodb.com/blog/post/revisiting-usdlookup and you can expect a 3.2 release candidate community build that includes $lookup in to be released soon.

Ron

Comment by Jason Singleton [ 23/Oct/15 ]

Making the API different between editions at this level is very bad, everyone has already stated the reasons so I won't reiterate.

But I also understand their side of needing profit making features.

Maybe it's time they started having different editions for all levels like SQL Server editions of Express, Standard, Developer and Enterprise. Which of course Express and Enterprise already exist and I do mean a paid Developer edition.

Comment by John A. De Goes [ 23/Oct/15 ]

bugslayer I actually agree with you. I don't think this is the right or best place to draw the line between CE and EE, for all the reasons listed above (and more). To prevent lock-in, to allow developers without EE to actually write applications using $lookup (locally and in test environments, etc.), to encourage open source projects to support $lookup, to compete with Couchbase, to draw a clear and consistent line between CE and EE features, and so on — all these and more are good reasons for putting $lookup in the community edition.

That said, the decision is really beyond our "pay grade", so to speak. So my above comments are more like, "OK, given that the issue appears to be decided, now what do we do?" I feel like, while it's a bit disappointing, it's not yet the end of the world.

Comment by John Crenshaw [ 23/Oct/15 ]

jdegoes

I noticed this problem when I asked: "Does it make sense to upgrade to EE for this?". The answer is that no, it doesn't, for the reasons mentioned.

I didn't claim the end of the world, but there is a specific aspect of the way software is developed in the real world that they clearly forgot about with this decision, which makes this not only a disservice to community users, but also to enterprise users. With the current constraints, $lookup has very limited use in application development outside of MongoDB, Inc., even when those applications use Enterprise Edition on their production servers. Highlighting that problem on the thread that is used to discuss the correctness of the design is appropriate and constructive.

I am plenty familiar with alternatives to having a $lookup, but that seems rather beside the point on a thread about whether the $lookup that has actually been implemented can work for anyone, don't you think?

Comment by John A. De Goes [ 22/Oct/15 ]

debrando bugslayer fgrillini nefiga sallgeud

Of course, as a developer on the community edition, I prefer $lookup to be in the community edition, but I understand MongoDB has their reasons.

It's not the end of the world, however, as some comments above suggest. For point joins and analytic / reporting joins, you do have great alternatives. Read more about them on this blog post: MongoDB and the Case of the Missing JOIN

Comment by Denis Brandolini [ 22/Oct/15 ]

I think John Crenshaw totally got the point, but let me add more from my company perspective.

We supply our software in a flexible way to address specific customers needs, from in-house self installation and maintenance to full-feature SaaS. Some customer is big and can afford a full enterprise installation, but someone else have small budget and we cannot just say "We're sorry, oure next release needs the EE: you'll have to pay extra 20k€/year". On the other hands, forking our software to support the new feature only if avaible would not be an option. This mean that for at least two years we couldn't use the $lookup.

The main objection I must face against a wider adoption of MongoDB in our software is exactly the lack of some form of join in order to address reporting needs, and saying we could have it but only with the most expensive license would be a little funny because we're evalutaing to move reporting away from OracleDB exactly to cut costs and leaving the painful forked development caused by license-bonded features.

I understand the needs of monetize your wonderful product, but on my company the choice about $lookup licensing could ground our entire MongoDB adoption roadmap...

Comment by John Crenshaw [ 22/Oct/15 ]

The decision to limit this to enterprise makes the feature unusable, even for enterprise users. Stable modern software development requires that developers be able to run and test their code in local isolated environments. This means that developers have all of the necessary tooling running locally, on their own $500 laptop. For web development this typically includes a local web server instance (often Apache or IIS), a web development language of choice (often PHP, Node.js, Ruby, or .Net), and the DB server of choice (which you hope is will be MongoDB). These local environments will never include software that has a $10k licensing cost.

Because local environments need to be able to run the entire system, and because this language feature has been restricted to enterprise only, your enterprise users have to make a choice: a) violate the isolation of development environments (and accept the endless flood of problems that comes with that), or b) don't use the new $lookup language feature.

With only rare exception, the technology leaders in an organization would have to be bone dead stupid to choose option a.

I seriously hope you will consider how this actually impacts your users, especially in stable well designed development environments, and reconsider the decision to lock this language feature. To encourage enterprise deployments, the developers building those deployments need to be able to do their job, and this decision actually interferes with that goal rather than advancing it.

Comment by Federico Grillini [ 21/Oct/15 ]

I completely agree with Ben Rotz. In my company i have to fight to make some collegues understand that mongodb is more than a file storage system, because they think that a relational dbms has more powerful search capabilities.
This is a basic feature for a dbms, not an enterprise one.

Comment by Ben Rotz [ 11/Oct/15 ]

Please reconsider your decision about having $lookup in the enterprise edition only. The blog post didn't really offer a good reason for not having this functionality available in the non-enterprise build. And feature split with functions at this level makes me nervous about what next "most requested" feature is going to get the enterprise-only treatment.

Comment by Chad Kreimendahl [ 08/Oct/15 ]

That's got to be a joke.. it's not April 1. To ask for help from people like our team, and to suggest the reason the questions on functionality were being asked was to help us out, and to then take that feedback and valuable input and exclude those who had input is just wrong. This has bee one of the most requested items out there. The blog seems to be trying desperately to make excuses and avoid the real reasons this was done.

Comment by Ron Avnur [ 01/Oct/15 ]

Hi Mitar and Neville,

You’re correct that the $lookup aggregation stage is available in the enterprise builds. Please see the following blog post for a detailed explanation: https://www.mongodb.com/blog/post/thoughts-on-new-feature-lookup

Comment by NOVALUE Mitar [ 28/Sep/15 ]

Oh, enterprise version only? I thought that enterprise version does not differ in core features from community version?

Comment by Asya Kamsky [ 25/Sep/15 ]

We don't have explicit/scheduled plans, but it's a safe assumption that in future releases we will enhance this feature to allow more functionality and to reduce restrictions.

Those will be tracked in separate tickets though.

Comment by Neville Dipale [ 25/Sep/15 ]

Are there plans to make $lookup work across databases? I also see that it's only available on enterprise builds

Comment by Asya Kamsky [ 25/Sep/15 ]

The $lookup stage is analogous to a left outer join - based on the join condition, the number of documents from "other" collection that are brought into the pipeline may be 0, 1 or many. Since result is represented as an array, the array will have 0, 1 or multiple elements.

There are some examples in the 3.1 docs (though note these are not guaranteed to stay the same for final 3.2 release).
http://docs.mongodb.org/manual/release-notes/3.1-dev-series/#left-outer-join

Comment by NOVALUE Mitar [ 24/Sep/15 ]

Please, can you give some feedback on what will be semantics of this? It is being quietly implemented without any open discussion on its design. Can we discuss this? Is there a better place to discuss it?

For example, how does $lookup behave with missing fields? Can it be treated as outer join?

Comment by Githook User [ 11/Aug/15 ]

Author:

{u'username': u'jamesfcohan', u'name': u'James Cohan', u'email': u'james.cohan@10gen.com'}

Message: SERVER-19095 Adds aggregation stage $lookUp
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/0bb7e669884d0112818b10335361bff9b7f6504c

Comment by Githook User [ 11/Aug/15 ]

Author:

{u'username': u'jamesfcohan', u'name': u'James Cohan', u'email': u'james.cohan@10gen.com'}

Message: SERVER-19095 Changes parsing of stages in authorization checking and adds enterprise pipeline tests to aggregation suite
Branch: master
https://github.com/mongodb/mongo/commit/ff9624152b6b9d017d65cf5055dc5f3a9907f062

Comment by NOVALUE Mitar [ 08/Aug/15 ]

This is great work. What exactly will be the syntax and semantic of this operation? Is there any design doc?

Also, if $lookup is left outer join, how will you represent the missing relations? With null? Empty document? Missing field?

Comment by Githook User [ 07/Aug/15 ]

Author:

{u'username': u'jamesfcohan', u'name': u'James Cohan', u'email': u'james.cohan@10gen.com'}

Message: SERVER-19095 Fix formatting
Branch: master
https://github.com/mongodb/mongo/commit/34536a3631de2e6013b1e46d30895ac7086b2aec

Comment by Githook User [ 07/Aug/15 ]

Author:

{u'username': u'jamesfcohan', u'name': u'James Cohan', u'email': u'james.cohan@10gen.com'}

Message: SERVER-19095 Prep for $lookUp, add checks for auth and sharded collection(s)
Branch: master
https://github.com/mongodb/mongo/commit/9285c02fa61049b2a011a0f9f53ccfbc46a0facb

Comment by Mariano Vicario [ 21/Jul/15 ]

This is the feature that was missing Mongo!!!! MongoDB needs this!!!

I'm happy that this started to be a priority to MongoDB!

Comment by Neville Dipale [ 03/Jul/15 ]

This will be great!

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