CODEWITHSUNDEEP
mongodbmongoosemongodb-querymongoose-schema
Nilesh Mistry
Nilesh Mistry

Reputation: 337

MongoDB nested group by query

I want to count correct, incorrect and unattempted question count. I am getting zero values.

Query - 

db.studentreports.aggregate([
{ $match: { 'groupId': 314 } },
{ $unwind: '$questions' },
{ $group: 
    {
      _id: {
       dateTimeStamp: '$dateTimeStamp',
       customerId: '$customerId'
      },
      questions : { $push: '$questions' },
      unttempted : { $sum : { $eq: ['$questions.status',0]}},
      correct : { $sum : { $eq: ['$questions.status',1]}}, 
      incorrect : { $sum : { $eq: ['$questions.status',2]}}, 
      Total: { $sum: 1 }      
    }
}
])

Schema structure - 

{
    "_id" : ObjectId("59fb46ed560e1a2fd5b6fbf4"),    
    "customerId" : 2863318,
    "groupId" : 309,  
    "questions" : [ 
        {
            "questionId" : 567,
            "status" : 0,            
            "_id" : ObjectId("59fb46ee560e1a2fd5b700a4"),           
        }, 
        {
            "questionId" : 711,
            "status" : 0,           
            "_id" : ObjectId("59fb46ee560e1a2fd5b700a3")           
        }, 
....

values unttempted, correct and incorrect are getting wrong -

"unttempted" : 0, "correct" : 0, "incorrect" : 0, "Total" : 7558.0

Group by is required based on datetime and customerId. Can some one correct query ? Thanks.

Upvotes: 0

Views: 61

Answers (1)

Alex P.
Alex P.

Reputation: 3171

You want to sum these fields only if a certain condition is met. You just have to rewrite your group statement like this:

{ $group: 
    {
      _id: {
       dateTimeStamp: '$dateTimeStamp',
       customerId: '$customerId'
      },
      questions : { $push: '$questions' },
      unttempted : { $sum : {$cond:[{ $eq: ['$questions.status',0]}, 1, 0]}},
      correct : { $sum : {$cond:[{ $eq: ['$questions.status',1]}, 1, 0]}}, 
      incorrect : { $sum : {$cond:[{ $eq: ['$questions.status',2]}, 1, 0]}},
      Total: { $sum: 1 }      
    }
}

Check out the documentation $eq. $eq compares and returns true or false. So then your $sum cannot do anything with that result

Upvotes: 1

Related Questions