How to join multiple collections with $lookup in mongodb
MongodbJoinMongodb QueryMongodb Problem Overview
I want to join more than two collections in MongoDB using the aggregate $lookup
. Is it possible to join? Give me some examples.
Here I have three collections:
users
:
{
"_id" : ObjectId("5684f3c454b1fd6926c324fd"),
"email" : "[email protected]",
"userId" : "AD",
"userName" : "admin"
}
userinfo
:
{
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"phone" : "0000000000"
}
userrole
:
{
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"role" : "admin"
}
Mongodb Solutions
Solution 1 - Mongodb
The join feature supported by Mongodb 3.2 and later versions. You can use joins by using aggregate query.
You can do it using below example :
db.users.aggregate([
// Join with user_info table
{
$lookup:{
from: "userinfo", // other table name
localField: "userId", // name of users table field
foreignField: "userId", // name of userinfo table field
as: "user_info" // alias for userinfo table
}
},
{ $unwind:"$user_info" }, // $unwind used for getting data in object or for one record only
// Join with user_role table
{
$lookup:{
from: "userrole",
localField: "userId",
foreignField: "userId",
as: "user_role"
}
},
{ $unwind:"$user_role" },
// define some conditions here
{
$match:{
$and:[{"userName" : "admin"}]
}
},
// define which fields are you want to fetch
{
$project:{
_id : 1,
email : 1,
userName : 1,
userPhone : "$user_info.phone",
role : "$user_role.role",
}
}
]);
This will give result like this:
{
"_id" : ObjectId("5684f3c454b1fd6926c324fd"),
"email" : "[email protected]",
"userName" : "admin",
"userPhone" : "0000000000",
"role" : "admin"
}
Hope this will help you or someone else.
Thanks
Solution 2 - Mongodb
You can actually chain multiple $lookup stages. Based on the names of the collections shared by profesor79, you can do this :
db.sivaUserInfo.aggregate([
{
$lookup: {
from: "sivaUserRole",
localField: "userId",
foreignField: "userId",
as: "userRole"
}
},
{
$unwind: "$userRole"
},
{
$lookup: {
from: "sivaUserInfo",
localField: "userId",
foreignField: "userId",
as: "userInfo"
}
},
{
$unwind: "$userInfo"
}
])
This will return the following structure :
{
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"phone" : "0000000000",
"userRole" : {
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"role" : "admin"
},
"userInfo" : {
"_id" : ObjectId("56d82612b63f1c31cf906003"),
"userId" : "AD",
"phone" : "0000000000"
}
}
Maybe this could be considered an anti-pattern because MongoDB wasn't meant to be relational but it is useful.
Solution 3 - Mongodb
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" : "[email protected]",
"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"
}
}
])
Solution 4 - Mongodb
First add the collections and then apply lookup on these collections. Don't use $unwind
as unwind will simply separate all the documents of each collections. So apply simple lookup and then use $project
for projection.
Here is mongoDB query:
db.userInfo.aggregate([
{
$lookup: {
from: "userRole",
localField: "userId",
foreignField: "userId",
as: "userRole"
}
},
{
$lookup: {
from: "userInfo",
localField: "userId",
foreignField: "userId",
as: "userInfo"
}
},
{$project: {
"_id":0,
"userRole._id":0,
"userInfo._id":0
}
} ])
Here is the output:
/* 1 */ {
"userId" : "AD",
"phone" : "0000000000",
"userRole" : [
{
"userId" : "AD",
"role" : "admin"
}
],
"userInfo" : [
{
"userId" : "AD",
"phone" : "0000000000"
}
] }
Thanks.
Solution 5 - Mongodb
first lookup finds all the products where p.cid = categories._id,simlarly 2nd lookup finds all products where p.sid = subcategories._id.
let dataQuery :any = await ProductModel.aggregate([ { $lookup:{
from :"categories",
localField:"cid",
foreignField :"_id",
as :"products"
}
},
{
$unwind: "$products"
},
{ $lookup:{
from :"subcategories",
localField:"sid",
foreignField :"_id",
as :"productList"
}
},
{
$unwind: "$productList"
},
{
$project:{
productList:0
}
}
]);