How to find max and min value from embedded documents in Mongodb

Question

So i have this json file:

{"_id":190,"name":"Adrien Renda","scores":[{"score":64.16109192679477,"type":"exam"},{"score":66.93730600935531,"type":"quiz"},{"score":96.0560340227047,"type":"homework"}]}
{"_id":191,"name":"Efrain Claw","scores":[{"score":94.67153825229884,"type":"exam"},{"score":82.30087932110595,"type":"quiz"},{"score":75.86075840047938,"type":"homework"}]}
{"_id":192,"name":"Len Treiber","scores":[{"score":39.19832917406515,"type":"exam"},{"score":98.71679252899352,"type":"quiz"},{"score":44.8228929481132,"type":"homework"}]}
{"_id":193,"name":"Mariela Sherer","scores":[{"score":47.67196715489599,"type":"exam"},{"score":41.55743490493954,"type":"quiz"},{"score":70.4612811769744,"type":"homework"}]}
{"_id":194,"name":"Echo Pippins","scores":[{"score":18.09013691507853,"type":"exam"},{"score":35.00306967250408,"type":"quiz"},{"score":80.17965154316731,"type":"homework"}]}
{"_id":195,"name":"Linnie Weigel","scores":[{"score":52.44578368517977,"type":"exam"},{"score":90.7775054046383,"type":"quiz"},{"score":11.75008382913026,"type":"homework"}]}
{"_id":196,"name":"Santiago Dollins","scores":[{"score":52.04052571137036,"type":"exam"},{"score":33.63300076481705,"type":"quiz"},{"score":78.79257377604428,"type":"homework"}]}
{"_id":197,"name":"Tonisha Games","scores":[{"score":38.51269589995049,"type":"exam"},{"score":31.16287577231703,"type":"quiz"},{"score":79.15856355963004,"type":"homework"}]}
{"_id":198,"name":"Timothy Harrod","scores":[{"score":11.9075674046519,"type":"exam"},{"score":20.51879961777022,"type":"quiz"},{"score":64.85650354990375,"type":"homework"}]}
{"_id":199,"name":"Rae Kohout","scores":[{"score":82.11742562118049,"type":"exam"},{"score":49.61295450928224,"type":"quiz"},{"score":28.86823689842918,"type":"homework"}]}

in a mongodb collection. And i'm trying to read the maximum and minimum score of the last 5 students and display them. I'm using mongolite in r studio and i've tried this:

res2 = con$aggregate(
  '[{"$group":{"_id": "$_id", "MaxScore": {"$max": "$scores.score"}, "MinScore":{"$min":"$scores.score"}}},
  { "$sort" : { "_id" : -1} },
  {"$limit": 5}
  ]'
)

The sorting and limit work just fine but the scores come out wrong. I'm guessing because they're embedded documents but i have no idea how to fix it. This is the end result of the above command

Answer 1

You don't need to perform $group query to calculate $max / $min scores, you can calculate them during $project stage

db.collection.aggregate([
  {
    "$project": {
      "_id": 1,
      "MaxScore": {
        "$max": "$scores.score"
      },
      "MinScore": {
        "$min": "$scores.score"
      }
    }
  },
  {
    "$sort": {
      "_id": -1
    }
  },
  {
    "$limit": 5
  }
])

MongoPlayground

If you want $group code working, just add before $group stage $unwind operator like below:

db.collection.aggregate([
  {
    $unwind: "$scores"
  },
  {
    $group: {
      _id: "$_id",
      MaxScore: {
        $max: "$scores.score"
      },
      MinScore: {
        $min: "$scores.score"
      }
    }
  },
  {
    "$sort": {
      "_id": -1
    }
  },
  {
    "$limit": 5
  }
])

MongoPlayground