Working with MongoDB's $lookup Aggregator

A deep dive into the $lookup aggregator with examples of one-to-one, one-to-many, many-to-many, and nested relational "queries"

Working with MongoDB's $lookup Aggregator

I'm a SQL guy -- relational databases are the cat's meow, in my opinion.  I've never been enamored with NoSQL databases because many of the kinds of data I've worked with over the years are inefficiently expressed in a denormalized document database.  None-the-less, I occasionally take a look at what is going on in the NoSQL world, and was surprised to discover that as of MongoDB 3.2, released in December 2015, there is now a$lookup aggregator that supports left outer joins (one to many) of two collections.
This was very attractive to me because, in my work with creating a semantic database using a relational database, there were aspects of NoSQL that were very attractive, schema-less being the main one.  However, the inability to relate two NoSQL collections except through client-side filtering, was a show stopper for me.  Well, not so anymore.  While I'm working on an article (which will get published shortly) for implementing a semantic database in MongoDB, I thought I'd write something specifically about using the new $lookup aggregator and the things I've learned working with it.

The History of $lookup

September 30, 2015
It's interesting reading the blog posts about the $lookup operator.  As Eliot Horowitz first wrote:
Using a document model does not mean that joins aren't useful. There's no question that when data is being used for reporting, business intelligence, and analytics, joins are effective.
Although, one does have to consider what he writes as definitely biased towards document databases:
The relational database model has several theoretical merits, but also many real-world limitations, chiefly the need to join data together across tables. Joins impede performance, inhibit scaling, and introduce substantial technical and cognitive overhead into all but textbook examples.
Wow, really?  In my opinion, I think this demonstrates a lack of understanding of a normalized database schema!
So, September 30, 2015:
As such, it is offered as part of our MongoDB Enterprise Advanced subscription. Our philosophy continues to be that documents are the right model for application development.
This created quite the community backlash (from the responses to Eliot's post in September):
This is pretty ridiculous. Splitting up actual engine functions across the enterprise version is both confusing and maddening. This is unfortunate as it means I now will need to start looking for alternative document db solutions at this point, because going down this road means more stuff like this is going to happen in the future. - Nef
Disgusting. I don't see justification for it being moved to enterprise other than you think you can make money from it, since it's been requested for ages and contributed to by people from the outside for free - Nada
And other colorful comments!
October 20, 2015
As John A. De Goes wrote in October:
While denormalization of data often ends the need for mindless fracturing and re-assembling of stored objects (as is common in the relational world), there are valid use cases for joins, even in MongoDB...MongoDB’s choice to create an Enterprise-only pipeline operator, against the wishes of some in the open source community, will be an interesting decision to watch unfold.
October 29, 2015
Eliot writes:
Two weeks ago I announced that the new aggregation pipeline stage $lookup (a limited left-outer join operator) would be a feature available only in MongoDB Enterprise Advanced Server. Our rationale was that we viewed $lookup as an enabler of other Enterprise features and we were concerned that widespread availability of $lookup in the Community Edition would lead to users treating MongoDB like a relational database.
Technically, I can understand this thinking, but also comes to what I believe is a much more correct view on the matter:
Nonetheless, one thing is clear: this surprised our users unpleasantly, which is something we never want to do. We hear you. $lookup is going to be a community feature. Finding the principle that makes sense of this decision (and which can guide and explain future choices) is important to us, but not as important as the confidence of our community.
and:
We’re still concerned that $lookup can be misused to treat MongoDB like a relational database. But instead of limiting its availability, we’re going to help developers know when its use is appropriate, and when it’s an anti-pattern.
Very nice.  As Mr. De Goes updated his original blog post:
The below post is outdated. MongoDB shipped the $lookup operator with MongoDB Enterprise Advanced and its community version. We are thrilled to see that MongoDB revisited the decision. This demonstrates how it cares about the community and values their feedback, and this, in turn, goes a long way towards building and maintaining the kind of trust necessary to fully commit to the open source project for the long run.

What You'll Need to Try Out This Code

If you've never used MongoDB with C#, you'll need to:

• Download and install the MongoDB server
• Download and install the 2.2.0 or greater MongoDB .NET Driver
• Run the MongoDB server, mongod.exe, either in a console window or as a service
  • The 3.2 64 bit version of mongod.exe is typically found in C:\Program Files\MongoDB\Server\3.2\bin
• Optionally download and install RoboMongo, so you can inspect your collections in a nice GUI.

One to Many Join

I'm not going to debate the pros and cons of a document vs. relational database here.  Both have their strengths and weaknesses.  What this article is about is how to use the $lookup aggregator should you determine that you need relationality between two collections.
Let's take a basic,overly simple, example, one in which one collection holds a code (we'll use telephone country codes as an example) and the other collection which is a lookup table matching the code to a country name.
First, in the Mongo console, create three documents in the countryCode collection:

Hide   Copy Code

db.countryCode.insert([{code: 1}, {code: 20}, {code: 30}])

This creates three documents (all screenshots are from RoboMongo):

Next, create a lookup table pairing the country codes to country names:

Hide   Copy Code

db.countryCodeLookup.insert([{code: 1, name: "United States"}, {code: 20, name: "Egypt"}, {code: 30, name: "Greece"}])

This results in the following documents:

Now we can query join the two collections with the $lookup operator.  In this query, I'm removing the "_id" field for clarity:

Hide   Copy Code

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $project: {"code":1, "countryName.name":1, "_id":0} }
])

The result is that the two documents are joined:

In the screenshot above, you'll note that each countryName element contains an array documents.  You can see this by adding a second document with the same code:

Hide   Copy Code

db.countryCodeLookup.insert({code: 1, name: "Foobar"})

Resulting in (for code 1):

One to One Join

If we know that the relationship is 1:1, we can use the $unwind aggregator to deconstruct the array, flattening the return document:

Hide   Copy Code

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $unwind: "$countryName"},
{ $project: {"code":1, "countryName.name":1, "_id":0} }
])

This eliminates the inner array:

Notice though that countryName is still in a sub-document.  We can use a projection to eliminate that:

Hide   Copy Code

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $unwind: "$countryName"},
{ $project: {"code":1, "name": "$countryName.name", "_id":0} }
])

Resulting in:

So now we have a nicely formatted one-to-one set of documents, effectively recreating what one could do a SQL view that joins one table with a second lookup table.

Many To Many Joins

(Note: for this example, I borrowed from something I did very early on, so the case style of the collection and field names is not standard!)
We can also use the lookup operator to query a many to many relationship.  Let's say I have two people and share two phone numbers.  We'll create the data like this:

Hide   Copy Code

db.Person.insert({ID: 1, LastName: "Clifton", FirstName: "Marc"})
db.Person.insert({ID: 2, LastName: "Wagers", FirstName: "Kelli"})

db.Phone.insert({ID: 1, Number: "518-555-1212"})
db.Phone.insert({ID: 2, Number: "518-123-4567"})

Note that I'm using my own ID's here, which is purely illustrative, it makes it easier to write the test examples, but this technique works equally well with the object ID "_id" field.
Next, we'll create the many-to-many association document:

Hide   Copy Code

db.PersonPhone.insert({ID: 1, PersonID: 1, PhoneID: 1})
db.PersonPhone.insert({ID: 2, PersonID: 2, PhoneID: 1})
db.PersonPhone.insert({ID: 3, PersonID: 2, PhoneID: 2})

This results in a document with 3 records:

We can unwind the arrays and alias the document field paths for a somewhat simpler layout:

Hide   Copy Code

db.PersonPhone.aggregate([
{ $lookup: { from: "Person", localField: "PersonID", foreignField: "ID", as: "PersonName" } }, 
{ $lookup: { from: "Phone", localField: "PhoneID", foreignField: "ID", as: "PersonPhone" } }, 
{ $unwind: "$PersonName"},
{ $unwind: "$PersonPhone"},
{$project: {"LastName":"$PersonName.LastName", "FirstName":"$PersonName.FirstName", "PhoneNumber":"$PersonPhone.Number", _id:0}} ])

Nested Lookups

What if you have three collections, where collection A has a "foreign key" to collection B, and collection B has a "foreign key" to collection C?  Let's go back to our country code / country name collections and add a phone number which a separate country code, so we can then do a join like this:
phone number + country code + country name
It's somewhat artificial but it will clearly illustrate the "trick" to get this to work.  First, create a couple phone records:

Hide   Copy Code

db.phone.insert([{number: "555-1212", countryCode: 1}, {number: "851-1234", countryCode: 20}])

Note that we must unwind the each resulting document before proceeding with the next lookup:

Hide   Copy Code

db.phone.aggregate([
{ $lookup: {from: "countryCode", localField: "countryCode", foreignField: "code", as: "countryCode"} },
{ $unwind: "$countryCode"},
{ $lookup: {from: "countryCodeLookup", localField: "countryCode.code", foreignField: "code", as: "country"} }
])

If we don't do this, we don't get any records in our nested document:

Why is this?
As Blakes Seven wrote on Stack Overflow:
The $lookup aggregation pipeline stage will not work directly with an array. The main intent of the design is for a "left join" as a "one to many" type of join ( or really a "lookup" ) on the possible related data. But the value is intended to be singular and not an array.  Therefore you must "de-normalise" the content first prior to performing the $lookup operation in order for this to work. And that means using $unwind
It took me a bit find that solution!
Again, we can further denormalize the data with a final unwind and an alias field path projection:

Hide   Copy Code

db.phone.aggregate([
{ $lookup: {from: "countryCode", localField: "countryCode", foreignField: "code", as: "countryCode"} },
{ $unwind: "$countryCode"},
{ $lookup: {from: "countryCodeLookup", localField: "countryCode.code", foreignField: "code", as: "country"} },
{ $unwind: "$country"},
{ $project: {"number": "$number", "countryCode": "$countryCode.code", "country": "$country.name", "_id":0} }
])

Conclusion

The $lookup aggregator adds the ability to create normalized documents in the Mongo NoSQL paradigm.  In my opinion, this is a really useful feature, as it pushes to problem of relating collections to the server, where theoretically (I can use that work too, Eliot!) one can take advantage of indexing and other performance improvements.  Not to mention that the data that has to be brought down to the client to otherwise relate two or more collections is significantly reduced.
In general, I've really enjoyed my first real exploration into MongoDB.  The aggregator pipeline is quite fun to work with, and I'm very pleased that the ability to relate two or more collections now exists in MongoDB.

Limitations

As the documentation for $lookup states:
"Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing."
This means that the join only works:

1. On records on the same machine (unsharded)
2. On records on the same database -- you cannot perform lookups across databases.

It would be interesting to see if these two limitations are ever overcome.

Other NoSQL Databases that Support Joins

Certainly this feature is not unique to MongoDB.  As Mr. de Goes pointed out:
Mongoose, for example, has a feature called population that can easily and instantly do point lookups with a single line of code, and a neat plug-in is available that can perform the join automatically.