MongoDB多集合查询问题

x4_965be

从MongoDB 3.2版开始，支持使用$lookup()进行多集合联接查询

假设ClassMembers集合的文档结构类似如下文档：

{
   "ClassID":"CL0001";
   "StudentID":"S000001";
   "DateFrom":"2015-09-01";
   "DateTo":null
}
{
   "ClassID":"CL0002";
   "StudentID":"S000002";
   "DateFrom":"2015-09-01";
   "DateTo":null
}

Scores集合的文档结构类似如下文档：

{
   "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}]
}

如何查询ClassID为CL0001，TestID为T000001的全部学生的Chinese的成绩？

22265

@x4_965be



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'}}
      
])