MongoDB aggregate array of objects together by object id and count occurences

Question

I'm trying to figure out what I'm doing wrong, I have collected the following, "Subset of data", "Desired output"

This is how my data objects look

[{
  "survey_answers": [
    {
      "id": "9ca01568e8dbb247", // As they are, this is the key to groupBy
      "option_answer": 5, // Represent the index of the choosen option
      "type": "OPINION_SCALE" // Opinion scales are 0-10 (meaning elleven options)
    },
    {
      "id": "ba37125ec32b2a99",
      "option_answer": 3,
      "type": "LABELED_QUESTIONS" // Labeled questions are 0-x (they can change it from survey to survey)
    }
  ],
  "survey_id": "test"
},
{
  "survey_answers": [
    {
      "id": "9ca01568e8dbb247",
      "option_answer": 0,
      "type": "OPINION_SCALE"
    },
    {
      "id": "ba37125ec32b2a99",
      "option_answer": 3,
      "type": "LABELED_QUESTIONS"
    }
  ],
  "survey_id": "test"
}]

My desired output is:

[
  {
    id: '9ca01568e8dbb247'
    results: [
      { _id: 5, count: 1 },
      { _id: 0, count: 1 }
    ]
  },
  {
    id: 'ba37125ec32b2a99'
    results: [
      { _id: 3, count: 2 }
    ]
  }
]

Active query

Model.aggregate([
    {
        $match: {
            'survey_id': survey_id
        }
    },
    {
        $unwind: "$survey_answers"
    },
    {
        $group: {
            _id: "$survey_answers.option_answer",
            count: {
                $sum: 1
            }
        }
    }
])

Current output

[
    {
        "_id": 0,
        "count": 1
    },
    {
        "_id": 3,
        "count": 2
    },
    {
        "_id": 5,
        "count": 1
    }
]

I don't think it will help I'm to far off from the result xD but I will share what I have - but thanks for the quick response — Simon Dragsbæk
– Simon Dragsbæk, Commented Jan 11, 2019 at 8:29

Ravi Kumar Gupta · Accepted Answer · 2019-01-11 09:29:13Z

I added your records to my db. Post that I tried your commands one by one.

$unwind results you similar to -

> db.survey.aggregate({$unwind: "$survey_answers"})

{ "_id" : ObjectId("5c3859e459875873b5e6ee3c"), "survey_answers" : { "id" : "9ca01568e8dbb247", "option_answer" : 5, "type" : "OPINION_SCALE" }, "survey_id" : "test" }
{ "_id" : ObjectId("5c3859e459875873b5e6ee3c"), "survey_answers" : { "id" : "ba37125ec32b2a99", "option_answer" : 3, "type" : "LABELED_QUESTIONS" }, "survey_id" : "test" }
{ "_id" : ObjectId("5c3859e459875873b5e6ee3d"), "survey_answers" : { "id" : "9ca01568e8dbb247", "option_answer" : 0, "type" : "OPINION_SCALE" }, "survey_id" : "test" }
{ "_id" : ObjectId("5c3859e459875873b5e6ee3d"), "survey_answers" : { "id" : "ba37125ec32b2a99", "option_answer" : 3, "type" : "LABELED_QUESTIONS" }, "survey_id" : "test" }

I am not adding code for match since that is okay in your query as well

The grouping would be -

> db.survey.aggregate({$unwind: "$survey_answers"},{$group: { _id: { 'optionAnswer': "$survey_answers.option_answer", 'id':"$survey_answers.id"}, count: { $sum: 1}}})

{ "_id" : { "optionAnswer" : 0, "id" : "9ca01568e8dbb247" }, "count" : 1 }
{ "_id" : { "optionAnswer" : 3, "id" : "ba37125ec32b2a99" }, "count" : 2 }
{ "_id" : { "optionAnswer" : 5, "id" : "9ca01568e8dbb247" }, "count" : 1 }

You can group on $survey_answers.id to bring it into projection.

The projection is what you're missing in your query -

> db.survey.aggregate({$unwind: "$survey_answers"},{$group: { _id: { 'optionAnswer': "$survey_answers.option_answer", 'id':'$survey_answers.id'}, count: { $sum: 1}}}, {$project : {answer: '$_id.optionAnswer', id: '$_id.id', count: '$count', _id:0}})

{ "answer" : 0, "id" : "9ca01568e8dbb247", "count" : 1 }
{ "answer" : 3, "id" : "ba37125ec32b2a99", "count" : 2 }
{ "answer" : 5, "id" : "9ca01568e8dbb247", "count" : 1 }

Further you can add a group on id and add results to a set. And your final query would be -

db.survey.aggregate(
    {$unwind: "$survey_answers"},
    {$group: { 
        _id: { 'optionAnswer': "$survey_answers.option_answer", 'id':'$survey_answers.id'}, 
        count: { $sum: 1}
    }}, 
    {$project : {
        answer: '$_id.optionAnswer', 
        id: '$_id.id', 
        count: '$count',
        _id:0
    }}, 
    {$group: {
        _id:{id:"$id"},
        results: { $addToSet: {answer: "$answer", count: '$count'} }
    }},
    {$project : {
        id: '$_id.id',
        answer: '$results', 
        _id:0
    }})

Hope this helps.

AMAZING !! love it please let me know if we can connect on linkedin i like the way you solved it a lot - my biggest thanks
Glad I could help. Sure, we can connect - in.linkedin.com/in/kravigupta :)

Collectives™ on Stack Overflow

MongoDB aggregate array of objects together by object id and count occurences

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related