我对MongoDB很陌生,以下问题一直困扰着我。我有两个集合的结构如下。但是我无论如何都无法在学校集合上执行$lookup操作。通过阅读其他帖子,我确定将ObjectId用作参考以及外键字段。
以下是我的结构:
校友:
{
"_id": "john",
"items": [
{
"name": "John",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa3d5e4"),
"grad": true
},
{
"school": ObjectId("56de35ab520fc05b2fa00000"),
"grad": false
}
]
},
{
"name": "Johnny"
// notice no nested items, this doc should still be included in result
},
{
"name": "Jon",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa11111"),
"grad": false
}
]
}
]
}
学校
{
_id: ObjectId("56de35ab520fc05b2fa3d5e4"),
name: "Some University",
street: "ABC Boulevard"
}
我想要得到的:
{
"_id": "john",
"items": [
{
"name": "John",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa3d5e4"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa3d5e4"),
name: "Some University",
street: "ABC Boulevard"
}
},
{
"school": ObjectId("56de35ab520fc05b2fa00000"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa00000"),
name: "Another University",
street: "123 Boulevard"
}
}
]
},
{
name: "Johnny"
},
{
"name": "Jon",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa11111"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa11111"),
name: "Some University",
street: "ABC Boulevard"
}
}
]
}
]
}
我尝试过但无法实现的查询:
db.alumni.aggregate([
{$match: {_id: 'john'}},
{$lookup: {
from: 'schools',
localField: 'items.items.school',
foreignField: '_id',
as: 'schoolInfo'}}
])
非常感谢任何帮助!