如何在 mongodb 中使用 $lookup 连接多个集合

小开

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" : "admin@gmail.com",
"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"
}
}
])

小开

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.

小开

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" : "admin@gmail.com",
"userName" : "admin",
"userPhone" : "0000000000",
"role" : "admin"
}

Hope this will help you or someone else.

Thanks

小开

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.

小开

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
}
}
]);