Now that we've covered the basics of the New Expressive Lookup. Let's look at a slightly more in-depth example. Currently the air_airlines collection isn't quite right. Not all the names for the member airlines are the actual names. For example, KLM is actually KLM Royal Dutch Airlines. So when air_alliances where we issued this findOne query, we can see that KLM is in the airlines array. However, when we query our air_airlines collection for KLM, it looks like the iata information was used instead of the name when constructing our air alliances collection. As in a side, this is the code assigned by the International Air Transport Association. So, how do we find the incorrect names? With the new expressive lookup, it should be pretty easy. Since we can now bind variables to use in a pipeline and that pipeline executes over the looked up collection. We can check to see if the name of the airline in the air alliance's collection matches either the name alias, iata, or icao values and the documents in the air_airlines collection. Okay, let's start with this. To from I'm specifying air_airlines, in let I'm binding the value of the airlines field from the document in air_alliances to a key called maybe_name. And pipeline I'm simply matching to see if maybe name is in name alias, iata, or icao. The found field is in an empty array. That definitely doesn't seem right, let's look at the pipeline again. Okay, there was the mistake since I'm comparing two arrays the $in operator won't do trick instead, I'll make sure the size of the inner section of both arrays is greater than 0. Meaning at least one element with the same in both. So here I'm using the size expression to get the size of the intersection between these two arrays and ensuring that that value is greater than 0. All right, I'm getting results in the found array now, but it's a bit crowded. Let's trim it down so I only get the information I want. Specifically, I want the name the airline should be. Okay, we had the internal pipeline as below so far. With the addition of this project stage, where I shaped the return document to my needs. I just want what the name is and the name we referred to it by. To do that, I filter the maybe_name variable which is the airlines array from the air_alliances collection to get the isolated value by using array element 0. After this filter, all entries would be identical but 0 guarantees they will get at least one value. Okay, we're getting closer to our goal, we can see in there that the ref_name was KLM. But the name_is KLM Royal Dutch Airlines. Let's do a quick sanity check to make sure we're finding all the member airlines of our alliances. Okay I have the same structuring syntaxes before with the edition of this match stage. Where I specify this expression checking to see if the size of the airlines array does not equal the size of the found array, meaning we didn't find all member airlines. Okay, scrolling up though it, I see one document, two documents, and three documents. So that means not only are we referring to the airlines with the wrong name, we may have misspellings as well. So, let's see which ones need updating and which ones we didn't find. All right, I've added a final project stage. I'll get rid of the _id, retain the name of the alliance and display which airlines I didn't find and which ones need updating. Great, is it a weird results, and I can see there are some minor typos, such as here, and here. Scrolling down, I can also see names that need updating. KLM to KLM Royal Dutch Airlines and TAROM to Tarom. This is an excellent starting point to fixing errors in our data. Okay, let's summarize. As we saw, we are free to use nearly any aggregation stage and expression within the sub-pipeline of the $lookup stage with the only restrictions being those that are already in place for any pipeline. Remember, the sub-pipeline in $lookup executes in the context of the of the collection we are looking up. So, throughout this lesson, the sub-pipeline was executing in the context of the air_airlines collection. Using the new syntax to perform calculations and transformations within the sub-pipeline in combining with calculations and transformations on the main pipeline, allow for simplified operations that previously would be much more complicated, if not impossible.
