加入MongoDB上的多个集合

Question

我想连接两个集合，然后将其结果与另一个集合连接。如何做到这一点？

数据：

聊天

{
"_id": ObjectId("5fd1ea22d605fede58184150"),
"raid": ObjectId("5fd1ea22d605fede5818414e"),
"description": "",
"password": "",
"created": ISODate("2020-12-10T09:28:02.661Z"),
"messages": [ ],
}

突袭：

{
"_id": ObjectId("5fd1ea22d605fede5818414e"),
"expires": "2020-12-10T09:34:10.808Z",
"invites_left": NumberInt("3"),
"trainers": [
{
"_id": ObjectId("5fc375f7bf36c0adbd587578"),
"username": "dsffdddsfs",
"host": true
},
{
"_id": ObjectId("5fd257e3faafc618f3b29b07"),
"username": "prova2"
}
],
}

培训师：

{
"_id": ObjectId("5fd1e467d41457dc20a1f2f3"),
"email": "[e-mail]",
"password": "1",
"username": "dsffdddsfs",
"country": "ES",
"city": "Barcelona",
},
{
"_id": ObjectId("5fd1e9acd605fede58184149"),
"email": "[e-mail]",
"password": "1",
"username": "prova2",
"country": "ES",
"city": "Barcelona",
}

我想要一个这样的东西：

{
"_id": ObjectId("5fd1ea22d605fede58184150"),
"raid": ObjectId("5fd1ea22d605fede5818414e"),
"description": "",
"password": "",
"created": ISODate("2020-12-10T09:28:02.661Z"),
"messages": [ ],
"trainers": [
{
"email": "[e-mail]",
"password": "1",
"username": "dsffdddsfs",
"country": "ES",
"city": "Barcelona",
"host": true
},
{
"email": "[e-mail]",
"password": "1",
"username": "prova2",
"country": "ES",
"city": "Barcelona",
}
],
}

因此，首先我必须加入raid的聊天，然后加入trainers的结果(合并数据(。到目前为止，我已经做到了：

db.chats.aggregate([
{
$match: 
{
raid : ObjectId('5fd1ea22d605fede5818414e')
}
},
{
$lookup : 
{
from : "raids",
localField : "raid",
foreignField : "_id", 
as : "FoundRaid"
}
},
{
$unwind: "$FoundRaid"
},
{
$lookup : 
{
from : "FoundRaid.trainers",
localField : "FoundRaid.username",
foreignField : "username", 
as : "FoundTrainerTOTAL"
}
},
{             // merfing not works here yet
$addFields: {
trainers: {
$map: {
input: "$trainers",
as: "s",
in: {
$mergeObjects: [
"$$s",
{
$first: {
$filter: {
input: "$FoundTrainer",
cond: {
$eq: [
"$$s.username",
"$$this.username"
]
}
}
}
}
]
}
}
}
}
}
]);

这给了我不正确/不完整的数据，因为(缺少与培训师的联系(：

{
"_id": ObjectId("5fd1ea22d605fede58184150"),
"archived": false,
"tipus": "raid",
"raid": ObjectId("5fd1ea22d605fede5818414e"),
"description": "",
"password": "",
"created": ISODate("2020-12-10T09:28:02.661Z"),
"messages": [ ],
"FoundRaid": {
"_id": ObjectId("5fd1ea22d605fede5818414e"),
"expires": "2020-12-10T09:34:10.808Z",
"invites_left": NumberInt("3"),
"trainers": [
{
"_id": ObjectId("5fc375f7bf36c0adbd587578"),
"username": "dsffdddsfs",
"host": true
},
{
"_id": ObjectId("5fd257e3faafc618f3b29b07"),
"username": "prova2"
}
],
"__v": NumberInt("2")
},
"FoundTrainerTOTAL": [ ],
"trainers": null
}

如何做到这一点？如何使"；从"；查找另一个$lookup的结果？谢谢

Answer 1

• $lookup和raids集合
• $unwind解构raid结果
• $lookup与trainers集合
• $addFields合并两个对象数组，$map迭代trainers数组的循环，$reduce迭代raid.trainers数组的循环并检查条件并得到匹配结果，回到$map，将使用$mergeObjects合并对象
• 使用$$REMOVE删除raid字段

db.charts.aggregate([
{
$lookup: {
from: "raids",
localField: "raid",
foreignField: "_id",
as: "raid"
}
},
{ $unwind: "$raid" },
{
$lookup: {
from: "trainers",
localField: "raid.trainers.username",
foreignField: "username",
as: "trainers"
}
},
{
$addFields: {
raid: "$$REMOVE",
trainers: {
$map: {
input: "$trainers",
as: "t",
in: {
$mergeObjects: [
"$$t",
{
host: {
$reduce: {
input: "$raid.trainers",
initialValue: null,
in: {
$cond: [
{ $eq: ["$$this.username", "$$t.username"] },
"$$this.host",
"$$value"
]
}
}
}
}
]
}
}
}
}
}
])

游乐场