关于数组字段的查询问题



  • 假设集合的文档结构如下:

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

    如何查询Score平均值最高的全部文档?上面示例文档有2个最高值

  • Lv 1

    @x4_965be

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

登录后回复
 

与 萌阔论坛 的连接断开,我们正在尝试重连,请耐心等待