MongoDB – get maximum value from result of avg aggregation

I have a collection of products, which all have reviews. I need to select the product with the highest review average. The problem is that I can group the products by average but I cannot group by average and select the product with the highest average.

To reproduce my issue, please follow these steps:

Insert product:

db.products.insert([
 {
     name: "Product1",
     price: 1000,
     features: {
         feature1: 0.8,
         feature2: 23
     },
     tags: ["tag1", "tag2", "tag3", "tag4"],
     assessments: [
         {name: "John", assessment: 3},
         {name: "Anna", assessment: 4},
         {name: "Kyle", assessment: 3.6}
     ]
 },
 {
     name: "Product2",
     price: 1200,
     features: {
         feature1: 4,
         feature2: 4000,
         feature3: "SDS"
     },
     tags: ["tag1"],
     assessments: [
         {name: "John", assessment: 5},
         {name: "Richard", assessment: 4.8}
     ]
 },
 {
     name: "Product3",
     price: 450,
     features: {
         feature1: 1.3,
         feature2: 60
     },
     tags: ["tag1", "tag2"],
     assessments: [
         {name: "Anna", assessment: 5},
         {name: "Robert", assessment: 4},
         {name: "John", assessment: 4},
         {name: "Julia", assessment: 3}
     ]
 },
 {
     name: "Product4",
     price: 900,
     features: {
         feature1: 1700,
         feature2: 17
     },
     tags: ["tag1", "tag2", "tag3"],
     assessments: [
         {name: "Monica", assessment: 3},
         {name: "Carl", assessment: 4}
     ]
 }
 ])
 

I want to group by average evaluation and select the product with the highest average.

I do this:

db.products.aggregate([
     { $unwind : "$assessments" },
     { $group:
         {
             _id: "$name",
             avg_assessment: {$avg: "$assessments.assessment"}
         }
     },
     { $project:
         {
             _id: 0,
             product: "$_id",
             avg_assessment: 1
         }
     }
 ])
 

The results of this query are:

{ "avg_assessment" : 3.5, "product" : "Product4" }
 { "avg_assessment" : 4, "product" : "Product3" }
 { "avg_assessment" : 4.9, "product" : "Product2" }
 { "avg_assessment" : 3.533333333333333, "product" : "Product1" }
 

Nice. Then I tried to select the product with the highest average using the following query:

db.products.aggregate([
         { $unwind : "$assessments" },
         { $group:
             {
                 _id: "$name",
                 avg_assessment: { $max: {$avg: "$assessments.assessment"}}
             }
         },
         { $project:
             {
                 _id: 0,
                 product: "$_id",
                 avg_assessment: 1
             }
         }
     ])
 

But the result is the same, but with rounded values:

{ "avg_assessment" : 4, "product" : "Product4" }
 { "avg_assessment" : 5, "product" : "Product3" }
 { "avg_assessment" : 5, "product" : "Product2" }
 { "avg_assessment" : 4, "product" : "Product1" }
 

What’s going on? What’s wrong?

1> user2683814..:

You can try the following aggregation. There is no $unwind required for this.

p>

Each evaluation of $avg is calculated and then sorted.

$group and $first select the appraisal with the highest average value.

Add $project stage to restrict fields.

db.products.aggregate([
   { "$addFields" : {"avg_assessment":{"$avg":"$assessments.assessment" }}},
   { "$sort":{"avg_assessment":-1}},
   { "$group":
     {
        "_id": null,
        "highest_avg_assessment": { $first:"$$ROOT"}
      }
    }
 ])