CODEWITHSUNDEEP
mongodbaggregation-framework
Puneet Behl
Puneet Behl

Reputation: 996

MongoDB: Calculate average of all the specific document's field which are embedded as list in a document

Here is the structure of document in mongodb:

{
    "_id" : NumberLong(1),
    "averageRating" : 2.5,
    "date" : ISODate("2013-11-12T02:15:14.448Z"),
    "filledBy" : "Degroote, Fred",
    "results" : [
        {
            "answer" : "Very caring doctor. I am happy",
            "name" : "comment",
            "type" : "string"
        },
        {
            "answer" : "4",
            "name" : "premises",
            "type" : "integer"
        },
        {
            "answer" : "5",
            "name" : "staff",
            "type" : "integer"
        },
        {
            "answer" : "0",
            "name" : "provider",
            "type" : "integer"
        },
        {
            "answer" : "1",
            "name" : "care",
            "type" : "integer"
        }
    ],
    "tenantId" : NumberLong(2),
    "token" : "38ae01f0-1203-4d10-9ff1-7c9fad1790cc",
    "version" : NumberLong(0)
}

We need to calculate average of answer's score for result name 'staff' in results of all documents having tenantId value 2.

The mongodb query, which we wrote is:

> db.surveyResults.aggregate({$match: {'tenantId': 2}}, {$unwind: '$results'}, {$match: {'results.type':'integer'}},{$group: {_id: '$results.name', averageSatisfaction: {$avg: '$results.answer'}}})

But, the problem in above mongodb query is that the field answer is of type string. Is there any way to solve this problem without changing the type of field answer?

Upvotes: 2

Views: 2620

Answers (1)

rubenfa
rubenfa

Reputation: 851

I think it is an ugly query but it works:

db.surveyResults.aggregate(
    {$match: {'tenantId': 2}}, 
    {$unwind: '$results'}, 
    {$match: {'results.type':'integer'}},
    {$project:{
        "results.answer":1,
        "results.name":1,
        one:{$cond:[{$eq:["$results.answer","1"]},1,0]},
        two:{$cond:[{$eq:["$results.answer","2"]},2,0]},
        three:{$cond:[{$eq:["$results.answer","3"]},3,0]},
        four:{$cond:[{$eq:["$results.answer","4"]},4,0]},
        five:{$cond:[{$eq:["$results.answer","5"]},5,0]},
        six:{$cond:[{$eq:["$results.answer","6"]},6,0]},
        seven:{$cond:[{$eq:["$results.answer","7"]},7,0]},
        eight:{$cond:[{$eq:["$results.answer","8"]},8,0]},
        nine:{$cond:[{$eq:["$results.answer","9"]},9,0]},
        }  
    },
    {$project: {
        "results.name":1,         
        sum:{$add:["$one","$two","$three","$four","$five","$six","$seven","$eight","$nine"]}        
     }},
     {$group: 
         {_id: '$results.name', averageSatisfaction: {$avg: '$sum'}}


        }

       )

Anyway the best option would be use integers as integers.

Upvotes: 1

Related Questions