Equality Match

To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:

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

The $lookup takes a document with the following fields:

The operation would correspond to the following pseudo-SQL statement:

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT *
                               FROM <collection to join>
                               WHERE <foreignField>= <collection.localField>);

See the following examples:

• Perform a Single Equality Join with $lookup
• Use $lookup with an Array
• Use $lookup with $mergeObjects

Join Conditions and Uncorrelated Sub-queries

To perform uncorrelated subqueries between two collections as well as allow other join conditions besides a single equality match, the $lookup stage has the following syntax:

{
   $lookup:
     {
       from: <collection to join>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to execute on the collection to join> ],
       as: <output array field>
     }
}

The $lookup takes a document with the following fields:

The operation would correspond to the following pseudo-SQL statement:

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                               FROM <collection to join>
                               WHERE <pipeline> );

See the following examples:

• Specify Multiple Join Conditions with $lookup
• Uncorrelated Subquery

Views and Collation

If performing an aggregation that involves multiple views, such as with $lookup or $graphLookup, the views must have the same collation.

Sharded Collection Restrictions

In the $lookup stage, the from collection cannot be sharded. However, the collection on which you run the aggregate() method can be sharded. That is, in the following:

db.collection.aggregate([
   { $lookup: { from: "fromCollection", ... } }
])

• The collection can be sharded.
• The fromCollection cannot be sharded.

As such, to join a sharded collection with an unsharded collection, you can run the aggregation on the sharded collection and lookup the unsharded collection; e.g.:

db.shardedCollection.aggregate([
   { $lookup: { from: "unshardedCollection", ... } }
])

Alternatively, or to join multiple sharded collections, consider:

• Modifying client applications to perform manual lookups instead of using the $lookup aggregation stage.
• If possible, using an embedded data model that removes the need to join collections.

Perform a Single Equality Join with $lookup

Create a collection orders with the following documents:

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
])

Create another collection inventory with the following documents:

db.inventory.insert([
   { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, description: "Incomplete" },
   { "_id" : 6 }
])

The following aggregation operation on the orders collection joins the documents from orders with the documents from the inventory collection using the fields item from the orders collection and the sku field from the inventory collection:

db.orders.aggregate([
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
])

The operation returns the following documents:

{
   "_id" : 1,
   "item" : "almonds",
   "price" : 12,
   "quantity" : 2,
   "inventory_docs" : [