【转载】Working with MongoDB's $look

原文出自：https://www.codeproject.com/Articles/1077839/Working-with-MongoDBs-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

<u>September 30, 2015</u>

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!

<u>October 20, 2015</u>

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.

<u>October 29, 2015</u>

Eliot writes:

Two weeks ago I announced that the new aggregation pipeline stage 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

<pre id="pre716173" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCode.insert([{code: 1}, {code: 20}, {code: 30}])</pre>

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

[图片上传中...(image-5a3691-1561085638698-10)]

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

Hide Copy Code

<pre id="pre449335" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCodeLookup.insert([{code: 1, name: "United States"}, {code: 20, name: "Egypt"}, {code: 30, name: "Greece"}])</pre>

This results in the following documents:

[图片上传中...(image-aaf646-1561085638698-9)]

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

<pre id="pre544088" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCode.aggregate([
{ project: {"code":1, "countryName.name":1, "_id":0} }
])</pre>

The result is that the two documents are joined:

[图片上传中...(image-76266a-1561085638698-8)]

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

<pre id="pre82142" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCodeLookup.insert({code: 1, name: "Foobar"})</pre>

Resulting in (for code 1):

[图片上传中...(image-971022-1561085638698-7)]

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

<pre id="pre551600" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCode.aggregate([
{ unwind: "project: {"code":1, "countryName.name":1, "_id":0} }
])</pre>

This eliminates the inner array:

[图片上传中...(image-f89763-1561085638698-6)]

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

Hide Copy Code

<pre id="pre407233" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.countryCode.aggregate([
{ unwind: "project: {"code":1, "name": "$countryName.name", "_id":0} }
])</pre>

Resulting in:

[图片上传中...(image-28ad08-1561085638698-5)]

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

<pre id="pre388666" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">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"})</pre>

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

<pre id="pre445069" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">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})</pre>

This results in a document with 3 records:

[图片上传中...(image-7cfa74-1561085638698-4)]

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

Hide Copy Code

<pre id="pre680521" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.PersonPhone.aggregate([
{ lookup: { from: "Phone", localField: "PhoneID", foreignField: "ID", as: "PersonPhone" } },
{ PersonName"},
{ PersonPhone"},
{PersonName.LastName", "FirstName":"PersonPhone.Number", _id:0}} ])</pre>

[图片上传中...(image-2a11ce-1561085638698-3)]

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

<pre id="pre78858" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.phone.insert([{number: "555-1212", countryCode: 1}, {number: "851-1234", countryCode: 20}])</pre>

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

Hide Copy Code

<pre id="pre50839" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.phone.aggregate([
{ unwind: "lookup: {from: "countryCodeLookup", localField: "countryCode.code", foreignField: "code", as: "country"} }
])</pre>

[图片上传中...(image-c657e-1561085638698-2)]

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

[图片上传中...(image-5b8152-1561085638698-1)]

Why is this?

As Blakes Seven wrote on Stack Overflow:

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

<pre id="pre862090" class="notranslate" style="color: rgb(0, 0, 0); background-color: rgb(251, 237, 187); padding: 6px; font: 13px Consolas, "Courier New", Courier, mono; white-space: pre-wrap; border: 1px solid rgb(251, 237, 187); tab-size: 4; overflow: auto; overflow-wrap: break-word; word-break: break-word; margin-top: 0px;">db.phone.aggregate([
{ unwind: "lookup: {from: "countryCodeLookup", localField: "countryCode.code", foreignField: "code", as: "country"} },
{ country"},
{ number", "countryCode": "country.name", "_id":0} }
])</pre>

[图片上传中...(image-11aebf-1561085638698-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

According to Wikipedia, the following NoSQL databases support joins (oddly, at the time of this writing, MongoDB is not in this list, probably because it doesn't support ACID (Atomicity, Consistency, Isolation, Durability):

• ArangoDB
• CouchDB
• c-treeAce
• HyperDex
• MarkLogic
• OrientDB

Regarding MongoDB and ACID, as per this response: "One thing you lose with MongoDB is multi-collection (table) transactions. Atomic modifiers in MongoDB can only work against a single document."

But the whole issue of ACID and transactions is a completely separate topic!