Count occurrences in nested mongodb document and keeping group

Question

I have theses documents:

[
  {
      "question": 1,
      "answer": "Foo"
  },
  {
      "question": 1,
      "answer": "Foo"
  },
  {
      "question": 1,
      "answer": "Bar"
  },
  {
      "question": 2,
      "answer": "Foo"
  },
  {
      "question": 2,
      "answer": "Foobar"
  }
]

And in my backend (php) I need to get the repartition of answers, something like:

• Question 1:

  • "Foo": 2/3
  • "Bar": 1/3

• Question 2:

  • "Foo": 1/2
  • "Foobar": 1/2

For now I just want to run a mongo query in order to achieve this result:

[
  {
      "question": 1,
      "answers": {
          "Foo": 2,
          "Bar": 1
      }
  },
  {
      "question": 2,
      "answers": {
          "Foo": 1,
          "Foobar": 1
      }
  }
 ]

Here is what I came up with:

db.getCollection('testAggregate').aggregate([{
    $group: {
        '_id': '$question',
        'answers': {'$push': '$answer'},
    }
}
]);

It returns:

{
    "_id" : 2.0,
    "answers" : [ 
        "Foo", 
        "Foobar"
    ]
},{
    "_id" : 1.0,
    "answers" : [ 
        "Foo", 
        "Foo", 
        "Bar"
    ]
}

And now I need to to a $group operation on the answers field in order to count the occurences, but I need to keep the group by question and I do not know how to do it. Could someone give me a hand?

Answer 1

You can use below aggregation.

Group by both question and answer to get the count for combination followed by group by question to get the answer and its count.

db.getCollection('testAggregate').aggregate([
  {"$group":{
    "_id":{"question":"$question","answer":"$answer"},
    "count":{"$sum":1}
  }},
  {"$group":{
    "_id":"$_id.question",
    "answers":{"$push":{"answer":"$_id.answer","count":"$count"}}
  }}
]);

You can use below code to get the format you want in 3.4.

Change $group keys into k and v followed by $addFields with $arrayToObject to transform the array into named key value pairs.

db.getCollection('testAggregate').aggregate([
  {"$group":{
    "_id":{"question":"$question","answer":"$answer"},
    "count":{"$sum":1}
  }},
  {"$group":{
    "_id":"$_id.question",
    "answers":{"$push":{"k":"$_id.answer","v":"$count"}}
  }},
 {"$addFields":{"answers":{"$arrayToObject":"$answers"}}}
]);