MongoDB多集合查询问题
-
从MongoDB 3.2版开始,支持使用$lookup()进行多集合联接查询
假设ClassMembers集合的文档结构类似如下文档:
{
Scores集合的文档结构类似如下文档:
"ClassID":"CL0001";
"StudentID":"S000001";
"DateFrom":"2015-09-01";
"DateTo":null
}
{
"ClassID":"CL0002";
"StudentID":"S000002";
"DateFrom":"2015-09-01";
"DateTo":null
}{
如何查询ClassID为CL0001,TestID为T000001的全部学生的Chinese的成绩?
"StudentID":"S000001";
"TestID":"T000001";
"Scores:":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":70}{"Course":"English","Score":75}]
}
{
"StudentID":"S000002";
"TestID":"T000001";
"Scores:":[{"Course":"Chinese","Score":70},{"Course":"Maths","Score":80}{"Course":"English","Score":82}]
}
-
db.ClassMembers.insertMany([
{
"ClassID":"CL0001",
"StudentID":"S000001",
"DateFrom":"2015-09-01"
},
{
"ClassID":"CL0002",
"StudentID":"S000002",
"DateFrom":"2015-09-01"
}
]); db.Scores.insertMany([
{
"StudentID":"S000001",
"TestID":"T000001",
"Scores":[{
"Course":"Chinese",
"Score":80
},{
"Course":"Maths",
"Score":70
},{
"Course":"English",
"Score":75
}]
},
{
"StudentID":"S000002",
"TestID":"T000001",
"Scores":[{
"Course":"Chinese",
"Score":70
},{
"Course":"Maths",
"Score":80
},{
"Course":"English",
"Score":82
}]
}
]); db.ClassMembers.aggregate([
{
$match: {ClassID: 'CL0001'}
},
{
$lookup:
{
from: "Scores",
localField: "StudentID",
foreignField: "StudentID",
as: "ScoresDocs"
}
},
{ $match : { "ScoresDocs.TestID" : 'T000001' }},
{ $project: { _id: 0, ClassID: 1, ScoresDocs: 1}},
{ $unwind: '$ScoresDocs'},
{ $unwind: '$ScoresDocs.Scores'},
{ $match: {'ScoresDocs.Scores.Course':'Chinese'}}
])