关于数组字段的查询问题
-
假设集合的文档结构如下:
{
如何查询Score平均值最高的全部文档?上面示例文档有2个最高值
"StudentID":"S0001",
"Scores":[{"Course":"Chinese","Score":75},{"Course":"Maths","Score":80},{"Course":"English","Score":78}]
}
{
"StudentID":"S0002",
"Scores":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":78},{"Course":"English","Score":75}]
}
{
"StudentID":"S0001",
"Scores":[{"Course":"Chinese","Score":78},{"Course":"Maths","Score":75},{"Course":"English","Score":72}]
}
-
db.yyy.insertMany([
{
"StudentID":"S0001",
"Scores":[{"Course":"Chinese","Score":75},{"Course":"Maths","Score":80},{"Course":"English","Score":78}]
},
{
"StudentID":"S0002",
"Scores":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":78},{"Course":"English","Score":75}]
},
{
"StudentID":"S0003",
"Scores":[{"Course":"Chinese","Score":78},{"Course":"Maths","Score":75},{"Course":"English","Score":72}]
}]) db.yyy.aggregate(
[
{
$unwind:"$Scores"
},
{
$group:{
_id: '$StudentID',
avg_score: {$avg: '$Scores.Score'}
}
},
{
$group:{
_id: '$avg_score',
StudentID: { $push : "$_id" }
}
},
{$sort: {_id: -1}},
{$limit: 1},
{$unwind: '$StudentID'},
{$project: {_id:0}}
]
);