Back to DocumentationMQL Documentation

$lookup

The `$lookup` stage in the Aggregation Framework is used to perform left outer joins with other collections. It allows you to combine documents from different collections based on a specified condition, enriching documents with related data.

Syntax

{
  $lookup: {
    from: <collection to join>,
    localField: <field from input documents>,
    foreignField: <field from the documents of the "from" collection>,
    as: <output array field>
  }
}

Parameters

fromstringrequired

The name of the collection to join with.

localFieldstringrequired

The field from the input documents that are matched with the foreignField.

foreignFieldstringrequired

The field from the documents in the from collection that are matched with the localField.

asstringrequired

The name of the new array field to add to the input documents containing the matched documents from the from collection.

Examples

Sample Data

Stores collection:
{
  "_id": "0fcc0bf0-ed18-4ab8-b558-9848e18058f4",
  "name": "First Up Consultants | Beverage Shop",
  "sales": { "totalSales": 75670 }
}

Ratings collection:
{
  "_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
  "rating": 5
}

Combine two collections to list stores with high ratings

Join the ratings collection with the stores collection to list promotion events for stores with a rating of 5.

This query performs a left outer join between ratings and stores collections.

Query:

db.ratings.aggregate([
  {
    $match: { "rating": 5 }
  },
  {
    $lookup: {
      from: "stores",
      localField: "_id",
      foreignField: "_id",
      as: "storeEvents"
    }
  },
  {
    $unwind: "$storeEvents"
  },
  { $project: { _id: 1, "storeEvents.name": 1 } }
])

Output:

[
  {
    "_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
    "storeEvents": { "name": "Lakeshore Retail | DJ Equipment Stop" }
  }
]

Joining collections using a pipeline

Join two collections using a variable from ratings and a pipeline for advanced filtering.

This query uses a pipeline within $lookup for more complex join logic.

Query:

db.ratings.aggregate([
  { $match: { rating: 5 } },
  {
    $lookup: {
      from: "stores",
      let: { id: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$id"] } } },
        { $project: { _id: 0, name: 1 } }
      ],
      as: "storeInfo"
    }
  },
  { $unwind: "$storeInfo" },
  { $project: { _id: 1, rating: 1, "storeInfo.name": 1 } }
])

Output:

[
  {
    "_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
    "rating": 5,
    "storeInfo": { "name": "Lakeshore Retail | DJ Equipment Stop" }
  }
]

Related