CODEWITHSUNDEEP
mongodbaggregation-framework
user
user

Reputation: 4840

MongoDB - get max from result of avg aggregation

I have collection of products and these products have assessments. I need select product with the highest average of assessment. The problem is I can group products by average but I cannot group by average and select product with highest average.

To reproduce my problem follow these steps:

Insert products:

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

And I want to group by avg of assessments and select product with max avg.

I do it following:

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

Result of this query is:

{ "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 try to select product with highest avg using 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 up 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? Where is a problem?

Upvotes: 0

Views: 1751

Answers (2)

s7vr
s7vr

Reputation: 75964

You can try below aggregation. No $unwind needed here.

Compute $avg for each assessment followed by sort desc.

$group with $first to pick the assessment with highest avg value.

Add $project stage to limit the fields.

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

Upvotes: 5

dnickless
dnickless

Reputation: 10918

This might help:

db.products.aggregate([
    { $unwind : "$assessments" },
    { $group: 
        {
            _id: "$name",
            avg_assessment: {$avg: "$assessments.assessment"}
        }
    },
    {
        $sort: { avg_assessment: -1 } // sort by avg_assessment descending
    },
    {
        $limit: 1 // only return one document
    }
])

Upvotes: 3

Related Questions