如何通过聚合从两个集合中获取数据

Question

我有两个集合

"plandesigns": [
{
"_id": ObjectId("6290e030a3f3e1177751e9ea"),
"planInfo": {
"states": [
{
"_id": ObjectId("6290e030a3f3e1177751e9ec"),
"stateId": ObjectId("5c1b5769e347b8652304a9a1"),
},
{
"_id": ObjectId("6290e030a3f3e1177751e9ed"),
"stateId": ObjectId("5c1b5769e347b8652304a9a3"),
}
]
},
}
]

和

"planstates": [
{
"_id": ObjectId("5c1b5769e347b8652304a9a1"),
"displayName": "California",
},
{
"_id": ObjectId("5c1b5769e347b8652304a9a3"),
"displayName": "New Jersey",
}
]

基本上，我需要根据状态从planddesigns集合中获取状态。但是，我需要最终结果包含来自states集合的displayName。

I tried this

db.plandesigns.aggregate([
{
$lookup: {
from: "planstates",
localField: "planInfo.states.stateId",
foreignField: "_id",
as: "states",

},

},
{
$unwind: "$states"
},
{
$project: {
"_id": "$states._id",
"displayName": "$states.displayName",

}
},

])

但是我不知道如何使用$project从planesdesigns获取数据，我该如何做到这一点?最终的结果应该是这样的:

{
// plandesign data,
"_id": ObjectId("6290e030a3f3e1177751e9ea"),
"planInfo": {
"states": [
{
"_id": ObjectId("6290e030a3f3e1177751e9ec"),
"stateId":ObjectId("5c1b5769e347b8652304a9a1"),
"displayName": "California",
}
]
},
}

Answer 1

db.plandesigns.aggregate([
{
$unwind: "$planInfo.states" //As it's an array, we need operation on one of array fields, we need to reshape it
},
{
$lookup: {
from: "planstates",
localField: "planInfo.states.stateId",
foreignField: "_id",
as: "states",

}
},
{
$unwind: "$states", //To project the states id, we need this

},
{
"$project": {
_id: 1,
stateId: "$planInfo.states._id",
displayName: "$states.displayName"
}
}
])

这是关于简单。您也可以从您的尝试中获得所需的格式，但这会导致更多的阶段，这将影响性能。