Surpise in how $lookup works

The homework problem with the air_alliances and air_routes really threw me for a loop. And I’m now realizing why:
In the homework solution, I can’t wrap my head around why the lookup works. Specifically, the lookup is seemingly working by equality matching localField to an ELEMENT OF the foreignField.

First, I would just like to confirm that this is indeed the case. Because reading the $lookup documentation, I can see no indication that this is how it SHOULD work.

Of course, it could come down to the fact that I’m not completely clear on how MongoDB does its equality matching - but I’m surprised that it worked as it did, since air_route.airline is within the air_alliances.airlines array - NOT equal to any instance of it.

1 Like

Good observation!

Basically, it’s actually not a feature of $lookup, but a feature of the equality condition, i.e. the explicit $eq operator or the implicit colon (:). Have a look at this.

And as you probably already know with $lookup, you can instead use a pipeline in place of the localField and foreignField statements for more flexibility.

1 Like

jb, I’m glad you brought up that alternate syntax. I’ve had my eye on it, but I’m missing how you specify the join condition. I’ll fiddle with it some.

Here’s an example using the two collections you were referring to:

db.air_alliances.aggregate([
  {
    $lookup: {
      from: "air_airlines",
      let: {all_airlines: "$airlines"},
      pipeline: [ 
        {
          $match: {
            country: {$in: ["Spain", "Canada"]},
            $expr: {$in: ["$name","$$all_airlines"]}
          }
        },
        {
          $project: {
            name: 1, country: 1, base: 1, _id: 0
          }
        }
      ],
      as: "alliance_airlines"
    }
  },
  {
    $limit: 3
  }
]).pretty()
  • For each alliance, find all airlines whose base country is Spain or Canada.
  • The result is returned in a sub-document called alliance_airlines
  • In order to reference the airlines field from the alliances collection, one must use the let statement; in this case I’ve aliased it as all_airlines… but I could call it the same name if I wished. Without let, you can’t reference local fields.
  • You use $ to reference foreign fields (i.e. fields from the airlines collection) and you use $$ to reference local fields declared in the let statement
  • $expr was used here because I needed to use the aggregation syntax for $in in a match stage. The query syntax for $in wouldn’t suffice because its input must be strings, whereas in this case I needed it to be expressions/field references.
  • Lastly, the $project stage ensures that I’m only returning relevant fields from the looked-up collection
1 Like

:exploding_head:

You hit pretty much every residual question I had left in the course. Thanks again, jb.
:bowing_man:

:slightly_smiling_face: :+1: