连接mongodb中的两个集合

Question

我是mongodb的新手。你能告诉我如何在这里面执行join操作吗?我有两个集合:

Collection 1 ("user")

{
_id: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
userName: "XYZ User",
age: 12
}

Collection 2 ("square")

{
_id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
side: 4,
area: 16
}

现在我想从集合2中检索数据，如下所示。预期的输出:

{
_id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
userName: "XYZ User",
side: 4,
area: 16
}

Thanks in advance:)

Answer 1

有一种方法。

db.square.aggregate([
{
"$lookup": {
"from": "user",
"localField": "userId",
"foreignField": "_id",
"as": "userDoc"
}
},
{
"$set": {
"userName": {
"$first": "$userDoc.userName"
}
}
},
{ "$unset": "userDoc" }
])

在mongoplayground.net上试试。

Answer 2

可以保留第二个文档中的第一个documentid (_id)作为userId作为参考，之后可以使用MongoDB 3.2及以后版本支持的join特性。可以通过使用聚合查询来使用连接。你可以使用下面的例子:

db.user.aggregate([
// Join with square table
{
$lookup:{
from: "square",       // other table name
localField: "_id",   // name of user table field
foreignField: "userId", // name of square table field
as: "square"         // alias for userinfo table
}
},
{   $unwind:"$user_info" },     // $unwind used for getting data in object or for one record only

// define some conditions here 
{
$match:{
$and:[{"userName" : "XYZ User"}]
}
},
// define which fields are you want to fetch
{   
$project:{
_id: 1,
userId: "$square.userId",
userName: 1,
side: "$square.side",
area: "$square.area"
} 
}
]);

结果将是

{
_id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
userName: "XYZ User",
side: 4,
area: 16
}

欢呼