带有不同localfield的mongodb$lookup

Question

我有三个不同的集合。以下示例。

Collection 1
{
_id: ObjectId('123'),
studentnumber: 123,
current_position: 'A',
schoolId: ObjectId('387')
}
Collection 2
{
_id: ObjectId('456'),
studentId: ObjectId('123'),
studentnumber: 123,
firstname: 'John',
firstname: 'Doe',
schoolId: ObjectId('543')
}
Collection 3
{
_id: ObjectId('387'),
schoolName: 'Some school'
},
{
_id: ObjectId('543'),
schoolName: 'Some other school'
},

我已经有一个聚合查询，看起来像这样。我对MongoDB聚合完全陌生。我想知道是否有任何方法可以在$lookup的localField中使用来自不同集合的字段。

db.collection1.aggregate([
///
$lookup: {
from: "collection2",
localField: "studentnumber",
foreignField: "studentnumber",
as: "studentnumber",
},
///
$lookup: {
from: "collection3",
localField: "schoolId",
foreignField: "_id",
as: "schoolId",
}
///
])

如何在localField中的第二个$lookup中使用来自collection2的schoolId

电流输出：

{
_id: ObjectId('123'),
firstname: 'John',
firstname: 'Doe',
current_position: 'A',
school: {
_id: ObjectId('387'),
schoolName: 'Some school'
}
}

预期输出：

{
_id: ObjectId('123'),
firstname: 'John',
firstname: 'Doe',
current_position: 'A',
school: {
_id: ObjectId('543'),
schoolName: 'Some other school'
}
}

编辑：更新了当前输出和预期输出。添加了输入文档。

Answer 1

您可以尝试$lookup的pipelined形式。像这样：

db.c1.aggregate([
{
"$lookup": {
"from": "c2",
"let": {
id: "$studentnumber"
},
"pipeline": [
{
"$lookup": {
"from": "c3",
"localField": "schoolId",
"foreignField": "_id",
"as": "school"
}
},
{
"$match": {
"$expr": {
"$eq": [
"$$id",
"$studentId"
]
}
}
}
],
"as": "doc"
}
},
{
"$addFields": {
"doc": {
"$arrayElemAt": [
"$doc",
0
]
}
}
},
{
"$addFields": {
"doc.school": {
"$arrayElemAt": [
"$doc.school",
0
]
}
}
},
{
"$project": {
"_id": 1,
"firstname": 1,
"lastname": 1,
"current_position": 1,
"school": "$doc.school"
}
}
])

在mongodb操场上查看这里的输出。