CODEWITHSUNDEEP
mongodbaggregation-framework
assaf_miz84
assaf_miz84

Reputation: 687

Mongo DB aggregation framework calculate avg documents

I have question collection each profile can have many questions.

{"_id":"..." , "pid":"...",.....}

Using mongo DB new aggregation framework how can I calculate the avg number of questions per profile?

tried the following without success:

{ "aggregate" : "question" , "pipeline" : [ { "$group" : { "_id" : "$pid" , "qCount" : { "$sum"    : 1}}} , { "$group" : { "qavg" : { "$avg" : "qCount"} , "_id" :  null }}]}

Can it be done with only one group operator?

Thanks.

Upvotes: 4

Views: 3939

Answers (1)

Derick
Derick

Reputation: 36794

For this you just need to know the amount of questions, and the amount of different profiles (uniquely identified with "pid" I presume). With the aggregation framework, you need to do that in two stages:

  • First, you calculate the number of questions per PID
  • Then you calculate the average of questions per PID

You'd do that like this:

Step one:

db.profiler.aggregate( [
    { $group: { _id: '$pid', count: { '$sum': 1 } } },
] );

Which outputs (in my case, with some sample data):

{
    "result" : [
        { "_id" : 2, "count" : 7 },
        { "_id" : 1, "count" : 1 },
        { "_id" : 3, "count" : 3 },
        { "_id" : 4, "count" : 5 }
    ],
    "ok" : 1
}

I have four profiles, respectively with 7, 1, 3 or 5 questions.

Now with this result, we run another group, but in this case we don't really want to group by anything, and thus do we need to set the _id value to null as you see in the second group below:

db.profiler.aggregate( [
    { $group: { _id: '$pid', count: { '$sum': 1 } } },
    { $group: { _id: null, avg: { $avg: '$count' } } }
] );

And then this outputs:

{
    "result" : [
        { "_id" : null, "avg" : 4 }
    ], 
    "ok" : 1
}

Which tells me that I have on average, 4 questions per profile.

Upvotes: 12

Related Questions