我有一个课程集合，其中我为该课程的每个科目分配教师。分配保存为JSON数组，请查看下面的参考文档。
{
"_id" : ObjectId("5cc7d72d8e165005cbef939e"),
"isAssigned" : true,
"name" : "11",
"section" : "A",
"allotment" : [
{
"subject" : ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
"teacher" : ObjectId("5cbee0e37a3c852868ec9797")
},
{
"subject" : ObjectId("5cc3f80e88e95a0c8e8ccd7e"),
"teacher" : ObjectId("5cbee10c7a3c852868ec9798")
}
]
}
我正在尝试匹配主题和教师字段以及来自两个不同集合的文档。我可以将它们放在两个不同的数组中，但无法获得预期输出中的结构化
教师收藏Doc
{
_id: ObjectId("5cbee0e37a3c852868ec9797"),
name: "Alister"
}
Doc主题
{
_id: ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
name: "English",
code: "EN"
}
我尝试过的查询
Course.aggregate([
{"$match": matchQuery},
{"$lookup": {
"from": "subjects",
"localField": "allotment.subject",
"foreignField": "_id",
"as": "subjectInfo"
}
},
{"$lookup": {
"from": "teachers",
"localField": "allotment.teacher",
"foreignField": "_id",
"as": "teacherInfo"}
},
])
该查询的输出
{
isAssigned: true
name: "11"
section: "A"
subjectInfo:[
{_id: "5cc3f7cc88e95a0c8e8ccd7d", name:"English", code:"EN"}
{_id: "5cc3f80e88e95a0c8e8ccd7e", name: "Science", code:"SC"}
]
teacherInfo:[
{_id: ObjectId("5cbee0e37a3c852868ec9797"),name: "Alister"},
{ _id: ObjectId("5cbee10c7a3c852868ec9798"),name: "Frank"}
]
}
输出输出
{
"_id" : ObjectId("5cc7d72d8e165005cbef939e"),
"isAssigned" : true,
"name" : "11",
"section" : "A",
"allotment" : [
{
"subject" : {
_id: ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
name: "English",
code: "EN"
}
"teacher" : {
_id: ObjectId("5cbee0e37a3c852868ec9797"),
name: "Alister"
}
},
{
"subject" : {
_id: ObjectId("5cc3f80e88e95a0c8e8ccd7e"),
name: "Science",
code: "SC"
}
"teacher" : {
_id: ObjectId("5cbee10c7a3c852868ec9798"),
name: "Frank"
}
}
]
}
只需在查找之前展开数组：
Course.aggregate([
{"$match": matchQuery},
{"$unwind: "$allotment"}
{"$lookup": {
"from": "subjects",
"localField": "allotment.subject",
"foreignField": "_id",
"as": "subjectInfo"
}
},
{"$lookup": {
"from": "teachers",
"localField": "allotment.teacher",
"foreignField": "_id",
"as": "teacherInfo"}
},
])
如果您想在此之后重新分组以恢复预期的格式，您可以添加：
{ $group : {
_id: "$_id",
name: {$first: "$name"},
section: {$first: "$section},
isAssigned: {$first: "$isAssigned},
allotment: {$push: {teacher: "$teacherInfo.0", subject: "$subjectInfo.0"}}
我假设教师信息和主题信息永远不会为空，如果不是这种情况，您应该添加一个$match来过滤空的。
看看$lookup聚合阶段，它允许您加入集合。留档中有很多关于用法的示例。
编辑：以下是应该提供预期结果的完整管道：
courses.aggregate(
[
{
"$unwind" : {
"path" : "$allotment"
}
},
{
"$lookup" : {
"from" : "subjects",
"localField" : "allotment.subject",
"foreignField" : "_id",
"as" : "allotment.subject"
}
},
{
"$lookup" : {
"from" : "teachers",
"localField" : "allotment.teacher",
"foreignField" : "_id",
"as" : "allotment.teacher"
}
},
{
"$addFields" : {
"allotment.subject" : {
"$arrayElemAt" : [
"$allotment.subject",
0.0
]
},
"allotment.teacher" : {
"$arrayElemAt" : [
"$allotment.teacher",
0.0
]
}
}
},
{
"$group" : {
"_id" : "$_id",
"isAssigned" : {
"$first" : "$isAssigned"
},
"name" : {
"$first" : "$name"
},
"section" : {
"$first" : "$section"
},
"allotment" : {
"$addToSet" : "$allotment"
}
}
}
]
)
首先，您必须$解除分配数组，然后为主题应用$lookup，然后为教师重复相同的操作，最后应用$group将其合并回数组中。请参阅下面的聚合查询，它已经尝试过并且对我有用。
Course.aggregate([
{"$match": matchQuery},
{
$unwind: '$allotment'
},
{
$lookup:{
"from": "subjects",
"localField": "allotment.subject",
"foreignField": "_id",
"as": "allotment.subject"
}
},
{
$unwind: '$allotment.subject'
},
{
"$lookup": {
"from": "teachers",
"localField": "allotment.teacher",
"foreignField": "_id",
"as": "allotment.teacher"
}
},
{
$unwind: '$allotment.teacher'
},
{
"$group" : {
"_id" : "$_id",
"isAssigned" : {
"$first" : "$isAssigned"
},
"name" : {
"$first" : "$name"
},
"section" : {
"$first" : "$section"
},
"allotment" : {
"$addToSet" : "$allotment"
}
}
}
])
