MongoDB - average of a feature after slicing the max of another feature in a group of documents-CodePudding

I am very new in mongodb and trying to work around a couple of queries, which I am not even sure if they 're feasible.

The structure of each document is:

{
     "_id"  : {
              "$oid": Text
              },
     "grade": Text,
     "type" : Text,
     "score": Integer,
     "info" : {
              "range"  : NumericText,
              "genre"  : Text,
              "special": {keys:values}
              }
};

The first query would give me:

per grade (thinking I have to group by "grade")
the highest range (thinking I have to call $max:$range, it should work with a string)
the score average (thinking I have to call $avg:$score)

I tried something like the following, which apparently is wrong:

collection.aggregate([{
                       '$group': {'_id':'$grade',
                                  'highest_range': {'$max':'$info',
                                  'average_score': {'$avg':'$score'}}}
                       }])

The second query would give the distinct genre records.

Any help is valuable!

ADDITION - providing an example of the document and the output:

{
     "_id"  : {
              "$oid": '60491ea71f8'
              },
     "grade": D,
     "type" : Shop,
     "score": 4,
     "info" : {
              "range"  : "2",
              "genre"  : 'Pet shop',
              "special": {'ClientsParking':True,
                          'AcceptsCreditCard':True,
                          'BikeParking':False}
                         }
};

And the output I am looking into is something within lines:

[{grade: A, "highest_range":"4", "average_score":3.5},
 {grade: B, "highest_range":"7", "average_score":8.3},
 {grade: C, "highest_range":"3", "average_score":2.4}]

CodePudding user response：

I think you are looking for this:

db.collection.aggregate([
   {
      '$group': {
         '_id': '$grade',
         'highest_range': { '$max': '$info.range' },
         'average_score': { '$avg': '$score' }
      }
   }
])

However, $min, $max, $avg works only on numbers, not strings. You could try { '$first': '$info.range' } or { '$last': '$info.range' }. But it requires $sort for proper result. Not clear what you mean by "highest range".