mongodb $group for nested array element

Question

I have following mongodb collection (questionanswers) for a given question

{ 
"_id" : { "$oid" : "59cda8b730a0131cfafc9101" }, 
"questionId" : { "$oid" : "59cb7b85c854036fec173267" }, 
"userQuestionAnswerDetails" : [ { "srno" : 1, "correct_yn" : false}, { 
 "srno" : 2, "correct_yn" : false} ] 
}

{ 
"_id" : { "$oid" : "59cdb1b930a0131cfafc9102" }, 
"questionId" : { "$oid" : "59cb7b85c854036fec173267" }, 
"userQuestionAnswerDetails" : [ { "srno" : 2, "correct_yn" : false} ] 
}

{ 
"_id" : { "$oid" : "59cdbb1b30a0131cfafc9105" }, 
"questionId" : { "$oid" : "59cb7b85c854036fec173267" }, 
"userQuestionAnswerDetails" : [ { "srno" : 2, "correct_yn" : false} ] 
}

{ 
"_id" : { "$oid" : "59cdd26a30a0131cfafc9107" }, 
"questionId" : { "$oid" : "59cb7b85c854036fec173267" }, 
"userQuestionAnswerDetails" : [ { "srno" : 1, "correct_yn" : false} ] 
}

I need cnt per sr no which is inside nested array element. Expected is:

Srno ===> cnt

1 ===> 2

2 ===> 3

I am trying with below query

db.questionanswers.aggregate([
{
    $match:{
        $and: [
            {questionId: ObjectId("59cb7b85c854036fec173267")}
        ]       
    }
},
{
    $group : {_id : {questionId:"$questionId", userQuestionAnswerDetails: "$userQuestionAnswerDetails.srno"}, num_tutorial : {$sum : 1}}
}
])

Above query is giving me something similar to below:

{"_id": {"questionId":"ObjectId(59cb7b85c854036fec173267)", "userQuestionAnswerDetails":[1,2]},"num_tutorial":1}
 {"_id":{"questionId":"ObjectId(59cb7b85c854036fec173267)", "userQuestionAnswerDetails":[1]},"num_tutorial":1}
{"_id":{"questionId":"ObjectId(59cb7b85c854036fec173267)", "userQuestionAnswerDetails":[2]},"num_tutorial":2}

which is equivalent to Srno ===> Cnt

1,2 ===> 1

1 ===> 1

2 ===> 2

How can I modify $group so as to achieve the expected result? Thanks in advance.

Serkan Arslan · Accepted Answer · 2017-10-03 07:52:40Z

3

You can make it by adding $unwind stage.

db.questionanswers.aggregate([
{
    $match:{
        $and: [
            {questionId: ObjectId("59cb7b85c854036fec173267")}
        ]       
    }
}
,{
    $unwind: "$userQuestionAnswerDetails" 
}
,{
    $group : {_id :  {questionId:"$questionId", userQuestionAnswerDetails: "$userQuestionAnswerDetails.srno"}, num_tutorial : {$sum : 1}}
}
])

Result:

{ "_id" : { "questionId" : ObjectId("59cb7b85c854036fec173267"), "userQuestionAnswerDetails" : 2 }, "num_tutorial" : 3 }
{ "_id" : { "questionId" : ObjectId("59cb7b85c854036fec173267"), "userQuestionAnswerDetails" : 1 }, "num_tutorial" : 2 }

answered Oct 3, 2017 at 7:52

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mongodb $group for nested array element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related