Pipeline $lookup with let and $expr

The basic $lookup can only match one field against another. The pipeline form runs a whole sub-pipeline against the foreign collection, which means you can filter during the join - and it is the form interviewers ask about.

Suppose:

users
orders

Question:

For each user, find only their completed orders.

You can use a pipeline inside $lookup.

{
  $lookup: {
    from: "orders",

    let: {
      userId: "$_id"
    },

    pipeline: [
      {
        $match: {
          $expr: {
            $and: [
              {
                $eq: [
                  "$userId",
                  "$$userId"
                ]
              },
              {
                $eq: [
                  "$status",
                  "completed"
                ]
              }
            ]
          }
        }
      }
    ],

    as: "orders"
  }
}

This looks scary, but break it down.

let

let: {
  userId: "$_id"
}

For the current user:

Current document:

{
  _id: 101,
  name: "Denish"
}

We create:

$$userId = 101

$expr

Normally:

{
  userId: 101
}

compares a field against a fixed value.

But here we want:

orders.userId
=
current user's _id

So we need expressions:

$expr: {
  $eq: [
    "$userId",
    "$$userId"
  ]
}

Important distinction:

$field
→ field from current pipeline document

$$variable
→ aggregation variable

This $lookup pattern is worth knowing.

Try it

db.users.aggregate([
  { $limit: 3 },
  {
    $lookup: {
      from: "orders",
      let: { userId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$userId", "$$userId"] },
                { $eq: ["$status", "completed"] }
              ]
            }
          }
        },
        { $project: { status: 1 } }
      ],
      as: "completedOrders"
    }
  },
  {
    $project: {
      name: 1,
      completedOrders: 1
    }
  }
])

Only completed orders came back. The basic $lookup would have joined all of them and left you to filter afterwards - same answer, more documents carried through the pipeline to get it.