mongodb多表查詢

標籤：foreign   english   any   rtm   field   cal   scores   course   aggregate   

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‘}}

])

mongodb多表查詢