MongoDB Count on matching strings in array grouped by array element

Question

I have a MongoDB collection storing answers to surveys. The answers are often radio buttons with responses like "Excellent", "Good", or "Poor". I'm attempting to produce a query that returns for each question the total number of a given response. The responses are currently stored in an array of strings. Position 0 in the array is the answer to question 1, and so forth.

I currently have an aggregation query that is returning data in the following TRUNCATED format:

[{ 
    "name" : "Medical Visit Survey", 
    "question" : [
        "Ease of making an appointment?", 
        "About how long was your wait time before being seen by the provider?", 
        "Professionalism of person who took your call?"
    ], 
    "type" : [ "radiobutton", "radiobutton", "radiobutton" ], 
    "answers" : [ "Excellent",  "Less than 20 minutes", "Excellent" ]
},
{ 
    "name" : "Medical Visit Survey", 
    "question" : [
        "Ease of making an appointment?", 
        "About how long was your wait time before being seen by the provider?", 
        "Professionalism of person who took your call?"
    ], 
    "type" : [ "radiobutton",  "radiobutton", "radiobutton" ], 
    "answers" : ["Excellent",  "Less than 20 minutes",  "Very Good" ]
}]

What is the best way to produce output similar to the following:

[{
   "name" : "Medical Visit Survey",
   "question" : "Ease of making an appointment?",
   "type" : "radiobutton",
   "answers": { 
                 "Excellent": 2,
                 "Good": 3,
                 "Poor": 1
              }
  
},
{
   "name" : "Medical Visit Survey",
   "question" : "About how long was your wait time before being seen by the provider?",
   "type" : "radiobutton",
   "answers": { 
                 "Less than 20 minutes": 2,
                 "More than 20 minutes": 3,
                 "More than 60 minutes": 1
              }
  
}
]

I've attempted queries similar to the following:

[
  {$unwind: "$answers" },
  { $group: { _id: "$answers", count: { $sum: 1 } } }
]

The output counts the responses based on the answers given, but does not take into account the question number ( element position in the array ).

I have a mongo playground link that may be helpful: https://mongoplayground.net/p/4_uM7khrMEM

Any assistance would be appreciated.

Answer 1

I am not sure is there any best way to so this, but i would suggest one aggregation query,

• $unwind deconstruct question array and include array index in index field in each element of question
• $arrayElemAt to select specific answer of provided index field and also same for type field
• $group by question and answer, select required fields and count total
• $group by only question and construct answers array in key-value format
• $arrayToObject convert answers array to object

[
  {
    $unwind: {
      path: "$question",
      includeArrayIndex: "index"
    }
  },
  {
    $group: {
      _id: {
        question: "$question",
        answer: { $arrayElemAt: ["$answers", "$index"] }
      },
      name: { $first: "$name" },
      type: { $first: { $arrayElemAt: ["$type", "$index"] } },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.question",
      answers: {
        $push: { k: "$_id.answer", v: "$count" }
      },
      name: { $first: "$name" },
      type: { $first: "$type" }
    }
  },
  { $addFields: { answers: { $arrayToObject: "$answers" } } }
]

Playground