- Reference >
- Operators >
- Aggregation Pipeline Operators >
- Pipeline Aggregation Stages >
- $lookup (aggregation)
$lookup (aggregation)¶
On this page
Definition¶
-
$lookup
¶ New in version 3.2.
Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. The
$lookup
stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection.To each input document, the
$lookup
stage adds a new array field whose elements are the matching documents from the “joined” collection. The$lookup
stage passes these reshaped documents to the next stage.The
$lookup
stage has the following syntax:The
$lookup
takes a document with the following fields:Field Description from
Specifies the collection in the same database to perform the join with. The from
collection cannot be sharded. For information, see Sharded Collection Restrictions.localField
Specifies the field from the documents input to the $lookup
stage.$lookup
performs an equality match on thelocalField
to theforeignField
from the documents of thefrom
collection. If an input document does not contain thelocalField
, the$lookup
treats the field as having a value ofnull
for matching purposes.foreignField
Specifies the field from the documents in the from
collection.$lookup
performs an equality match on theforeignField
to thelocalField
from the input documents. If a document in thefrom
collection does not contain theforeignField
, the$lookup
treats the value asnull
for matching purposes.as
Specifies the name of the new array field to add to the input documents. The new array field contains the matching documents from the from
collection. If the specified name already exists in the input document, the existing field is overwritten.
Consideration¶
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:
- 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.:
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.
Examples¶
Perform a Join with $lookup
¶
A collection orders
contains the following documents:
Another collection inventory
contains the following documents:
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:
The operation returns the following documents:
Use $lookup
with an Array¶
Starting MongoDB 3.4, if the localField
is an array, you can match
the array elements against a scalar foreignField
without needing an
$unwind
stage.
For example, create an example collection classes
with the
following document:
Create another collection members
with the following documents:
The following aggregation operation joins documents in the classes
collection with the members
collection, matching on the members
field to the name
field:
The operation returns the following: