$lookup
The MongoDB server $lookup performs a left outer join of one unsharded collection to another unsharded collection in the same database. Lookups are useful as they allow you to filter in documents from the "joined" collection for processing.
In federated database instance, you can use $lookup
to join sharded and unsharded
collections from the same database or different databases from Atlas,
AWS S3, and HTTP or HTTPS data stores.
Note
For sharded collections, $lookup
is only
available on Atlas clusters running MongoDB 5.1 and later.
Syntax
The $lookup
syntax is
described in the MongoDB server manual.
In Data Federation, the from
field in $lookup
has the
following alternate syntax. This allows you to specify an object that
contains an optional database name and a required collection name:
{ $lookup: { localField: "<fieldName>", from: <collection-to-join>|{db: <db>, coll: <collection-to-join>}, foreignField: "<fieldName>", as: "<output-array-field>", } }
{ $lookup: { from: <collection to join>|{db: <db>, coll: <collection-to-join>}, let: { <var_1>: <expression>, …, <var_n>: <expression> }, pipeline: [ <pipeline to execute on the collection to join> ], as: <output array field> } }
from
Field Object
Field | Type | Description | Necessity |
---|---|---|---|
db | string | The database name. If you specify a database name, Data Federation reads data from the collection in the specified database. If you specify a database name that differs from the database upon which the command is operating, all nested $lookup stages must also specify this database name. If you don't specify a database name within a $lookup stage, collections in the stage inherit the database name specified in the closest parent $lookup stage if it exists, or the name of the database upon which the command is operating. | Conditional |
coll | string | The collection name. | Required |
Examples
Suppose there are three databases named sourceDB1
, sourceDB2
, and
sourceDB3
with the following collections:
db.orders.insertMany([ { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 }, { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }, { "_id" : 3 } ])
db.catalog.insertMany([ { "_id" : 1, "sku" : "almonds", "description": "product 1" }, { "_id" : 2, "sku" : "bread", "description": "product 2" }, { "_id" : 3, "sku" : "cashews", "description": "product 3" }, { "_id" : 4, "sku" : "pecans", "description": "product 4" }, { "_id" : 5, "sku": null, "description": "Incomplete" }, { "_id" : 6 } ])
db.warehouses.insertMany([ { "_id" : 1, "stock_item" : "almonds", "warehouse": "A", "instock" : 120 }, { "_id" : 2, "stock_item" : "pecans", "warehouse": "A", "instock" : 70 }, { "_id" : 3, "stock_item" : "cashews", "warehouse": "B", "instock" : 60 }, { "_id" : 4, "stock_item" : "bread", "warehouse": "B", "instock" : 80 }, { "_id" : 5, "stock_item" : "cookies", "warehouse": "A", "instock" : 80 } ])
The following examples use the $lookup aggregation stage to join documents from one collection with the documents from the collection in the other databases.
Basic Example
The following aggregation operation on the sourceDB1.orders
collection joins the documents from the orders
collection with the documents
from the sourceDB2.catalog
collection using the item
field from the orders
collection and the sku
field from the catalog
collection:
db.getSiblingDb("sourceDB1").orders.aggregate( { $lookup: { from: { db: "sourceDB2", coll: "catalog" }, localField: "item", foreignField: "sku", as: "inventory_docs" } } )
Nested Example
The following aggregation operation on the sourceDB1.orders
collection joins the documents from the orders
collection with the documents from the sourceDB2.catalog
collection and the documents from the sourceDB3.warehouses
collection using the item
field from the orders
collection, the sku
field from the catalog
collection, and the stock_item
and instock
fields from the warehouses
collection:
db.getSiblingDb("sourceDB1").orders.aggregate( [ { $lookup: { from: db: "sourceDB2", coll: "catalog", let: { "order_sku": "$item" }, pipeline: [ { $match: { $expr: { $eq: ["$sku", "$$order_sku"] } } }, { $lookup: { from: db: "sourceDB3", coll: "warehouses", pipeline: [ { $match: { $expr:{ $eq : ["$stock_item", "$$order_sku"] } } }, { $project : { "instock": 1, "_id": 0} } ], as: "wh" } }, { "$unwind": "$wh" }, { $project : { "description": 1, "instock": "$wh.instock", "_id": 0} } ], as: "inventory" }, }, ] )