According to the documentation, $lookup can join only one external collection.
What you could do is to combine userInfo
and userRole
in one collection, as provided example is based on relational DB schema. Mongo is noSQL database - and this require different approach for document management.
Please find below 2-step query, which combines userInfo with userRole - creating new temporary collection used in last query to display combined data. In last query there is an option to use $out and create new collection with merged data for later use.
create collections
db.sivaUser.insert[
{
"_id" : ObjectId["5684f3c454b1fd6926c324fd"],
"email" : "",
"userId" : "AD",
"userName" : "admin"
}]
//"userinfo"
db.sivaUserInfo.insert[
{
"_id" : ObjectId["56d82612b63f1c31cf906003"],
"userId" : "AD",
"phone" : "0000000000"
}]
//"userrole"
db.sivaUserRole.insert[
{
"_id" : ObjectId["56d82612b63f1c31cf906003"],
"userId" : "AD",
"role" : "admin"
}]
"join" them all :-]
db.sivaUserInfo.aggregate[[
{$lookup:
{
from: "sivaUserRole",
localField: "userId",
foreignField: "userId",
as: "userRole"
}
},
{
$unwind:"$userRole"
},
{
$project:{
"_id":1,
"userId" : 1,
"phone" : 1,
"role" :"$userRole.role"
}
},
{
$out:"sivaUserTmp"
}
]]
db.sivaUserTmp.aggregate[[
{$lookup:
{
from: "sivaUser",
localField: "userId",
foreignField: "userId",
as: "user"
}
},
{
$unwind:"$user"
},
{
$project:{
"_id":1,
"userId" : 1,
"phone" : 1,
"role" :1,
"email" : "$user.email",
"userName" : "$user.userName"
}
}
]]
I have two collections, products
and properties
.
I'm doing a lookup such as:
[{
$match: {
category_id: mongoose.Types.ObjectId[category_id]
}
},
{
$lookup: {
from: "properties",
localField: "category_id",
foreignField: "category_id",
as: "properties"
}
}
]
This is basically getting me all of the products that match the category_id and including the properties that match the same category_id.
I need to add an additional check, for some_id
on the properties
result. In otherwords, the properties
should be grouped by the some_id
that is returned from the products
collection and matches the same key in properties
. Does that make sense?
Basically having the ability to have multiple local/foreign field definitions.
any idea how I could this?
Docs Home → MongoDB Manual
On this page
- Definition
- Syntax
- Equality Match with a Single Join Condition
- Join Conditions and Subqueries on a Joined Collection
- Correlated Subqueries Using Concise Syntax
- Behavior
- Views and Collation
- Restrictions
- Atlas Search Support
- Sharded Collections
- Slot-Based Query Execution Engine
- Examples
- Perform a Single Equality Join with
$lookup
- Use
$lookup
with an Array - Use
$lookup
with$mergeObjects
- Perform Multiple Joins and a Correlated Subquery with
$lookup
- Perform
an Uncorrelated Subquery with
$lookup
- Perform a Concise Correlated Subquery with
$lookup
$lookup
Changed in version 5.1.
Performs a left outer join to a collection in the same database to filter in documents from the "joined" collection for processing. The $lookup
stage adds a new array field to each input document. The new array field contains the matching documents from the "joined" collection. The $lookup
stage passes these reshaped documents to the next stage.
Starting in MongoDB 5.1, $lookup
works across sharded collections.
To combine elements from two different collections, use the $unionWith
pipeline stage.
The $lookup
stage has the following syntaxes:
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
this syntax:
{ $lookup: { from: , localField: , foreignField: , as: } }
The $lookup
takes a document with these fields:
from | Specifies the collection in the same database to perform the join with.
Starting in MongoDB 5.1, the collection specified in the |
localField | Specifies the field from the documents input to the |
foreignField | Specifies the field from the documents in the |
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 |
The operation would correspond to the following pseudo-SQL statement:
SELECT *, FROM collection WHERE IN [ SELECT * FROM WHERE = ];
See these examples:
Perform a Single Equality Join with
$lookup
Use
$lookup
with an ArrayUse
$lookup
with$mergeObjects
MongoDB 3.6 adds support for:
Executing a pipeline on a joined collection.
Multiple join conditions.
Correlated and uncorrelated subqueries.
In MongoDB, a correlated subquery is a pipeline in a $lookup
stage that references document fields from a joined collection. An uncorrelated subquery does not reference joined fields.
Note
Starting in MongoDB 5.0, for an uncorrelated subquery in a $lookup
pipeline stage containing a $sample
stage, the $sampleRate
operator, or the $rand
operator, the subquery is always run again if repeated.
Previously, depending on the subquery output size, either the subquery output was cached or the subquery was run again.
MongoDB correlated subqueries are comparable to SQL correlated subqueries, where the inner query references outer query values. An SQL uncorrelated subquery does not reference outer query values.
MongoDB 5.0 also supports concise correlated subqueries.
To perform correlated and uncorrelated subqueries with two collections, and perform other join
conditions besides a single equality match, use this $lookup
syntax:
{ $lookup: { from: , let: { : , …, : }, pipeline: [ ], as: } }
The $lookup
stage accepts a document with these fields:
from | Specifies the collection in the same database to perform the join operation.
Starting in MongoDB 5.1, the |
let | Optional. Specifies variables to use in the pipeline stages. Use the variable expressions to access the fields from the joined collection's documents that are input to the NoteTo reference variables in pipeline stages, use the The let variables can be accessed by the stages in the pipeline, including additional
|
pipeline
Specifies the pipeline
to run on the joined collection. The pipeline
determines the resulting documents from the joined collection. To return all documents, specify an empty pipeline
[]
.
The pipeline
cannot include the $out
stage or the $merge
stage. Starting in v6.0, the pipeline
can contain the Atlas Search $search
stage as the first stage inside the pipeline. To learn more, see
Atlas Search Support.
The pipeline
cannot directly access the joined document fields. Instead, define variables for the joined document fields using the let option and then reference the variables in the pipeline
stages.
Note
To reference variables in pipeline stages, use the "$$"
syntax.
The let variables can be accessed by the stages in the pipeline, including additional $lookup
stages
nested in the pipeline
.
A
$match
stage requires the use of an$expr
operator to access the variables. The$expr
operator allows the use of aggregation expressions inside of the$match
syntax.Starting in MongoDB 5.0, the
$eq
,$lt
,$lte
,$gt
, and$gte
comparison operators placed in an$expr
operator can use an index on thefrom
collection referenced in a$lookup
stage. Limitations:Multikey indexes are not used.
Indexes are not used for comparisons where the operand is an array or the operand type is undefined.
Indexes are not used for comparisons with more than one field path operand.
Other [non-
$match
] stages in the pipeline do not require an$expr
operator to access the variables.
as
Specifies the name of the new array field to add to the joined documents. The new array field contains the matching documents from the joined collection. If the specified name already exists in the joined document, the existing field is overwritten.