skip to Main Content

I am working on mongodb to build a simple survey app, the hardest part is to get the statistic using mongodb ‘s aggregation framework.

Here is one submit answers of the survey. The answers property is a array carrying the answers of each question .

{
   uid:'xxxx',
   surveyId:'xxxxx',
   answers:[
     {   answer: A },    // question 1 ‘s answer
     {   answer: B },    // question 2 ‘s answer 
     {   answer: C }      // question 3 ‘s answer 
   ]
}

The final result I want to have is to know the overall statistic of the survey.

   Question 1:
   A  50%  B 40% C 10%
   Question 2:
   A  60%  B 40% C 0%

The tricky part of mongodb aggregation is how to deal the ‘array’

2

Answers


  1. A simple option is to $unwind and $group:

    db.collection.aggregate([
      {$unwind: {path: "$answers", includeArrayIndex: "index"}},
      {$group: {
          _id: {
            surveyId: "$surveyId",
            index: "$index"
          },
          A: {$sum: {$cond: [{$eq: ["$answers.answer", "A"]}, 1, 0]}},
          B: {$sum: {$cond: [{$eq: ["$answers.answer", "B"]}, 1, 0]}},
          C: {$sum: {$cond: [{$eq: ["$answers.answer", "C"]}, 1, 0]}},
          all: {$sum: 1}
      }},
      {$project: {
          A: {$multiply: [{$divide: ["$A", "$all"]}, 100]},
          B: {$multiply: [{$divide: ["$B", "$all"]}, 100]},
          C: {$multiply: [{$divide: ["$C", "$all"]}, 100]},
          question: {$add: ["$_id.index", 1]},
          surveyId: "$_id.surveyId",
          _id: 0
      }}
    ])
    

    See how it works on the playground example

    Or a more generic approach can use $group twice without knowing the answer options for each question:

    db.collection.aggregate([
      {$unwind: {path: "$answers", includeArrayIndex: "index"}},
      {$group: {
          _id: {
            surveyId: "$surveyId",
            index: "$index",
            res: "$answers.answer"
          },
          count: {$sum: 1}
      }},
      {$group: {
          _id: {
            surveyId: "$_id.surveyId",
            index: "$_id.index"
          },
          data: {$push: {answer: "$_id.res", count: "$count"}},
          all: {$sum: "$count"}
      }},
      {$project: {
          data: {
            $map: {
              input: "$data",
              in: {
                answer: "$$this.answer",
                percent: {$multiply: [{$divide: ["$$this.count", "$all"]}, 100]}
              }
            }
          },
          question: {$add: ["$_id.index", 1]},
          surveyId: "$_id.surveyId",
          _id: 0
      }}
    ])
    

    See how it works on the playground example

    Login or Signup to reply.
  2. > db.collection.find({},{"_id":0});
    < { uid: 'xxxx',
        surveyId: 'xxx',
        answers: [ { answer: 'A' }, { answer: 'B' }, { answer: 'C' } ] }
      { uid: 'xxxx',
        surveyId: 'xxx',
        answers: [ { answer: 'B' }, { answer: 'C' }, { answer: 'C' } ] }
      { uid: 'xxxx',
        surveyId: 'xxx',
        answers: [ { answer: 'B' }, { answer: 'B' }, { answer: 'B' } ] }
      { uid: 'xxxx',
        surveyId: 'xxx',
        answers: [ { answer: 'B' }, { answer: 'A' }, { answer: 'C' } ] }
      { uid: 'xxxx',
        surveyId: 'xxx',
        answers: [ { answer: 'B' }, { answer: 'B' }, { answer: 'C' } ] }
    

    Using custom $accumulator,

    > db.collection.aggregate([
      {
          $group: {
              "_id": "$surveyId",
              "statistics": {
                  $accumulator: {
                      initArgs: [3],                        //number of questions as argument for the init function
                      init: function(questions){            //initialise 3 arrays for each question, with all counts set to 0
                          let answerArray = new Array();
                          for(i=1;i<=questions;i++){
                              answerArray.push({"question":i,"A":0,"B":0,"C":0,"total":0});
                          }
                          return answerArray;
                      },
                      accumulateArgs: ["$answers"],         //answers list as argument for accumulate function
                      accumulate: function(state, answers) {            //increment the counter/total for each answer
                          for(i=0;i<answers.length;i++){
                              switch(answers[i].answer){
                                  case "A": state[i].A = state[i].A+1; break;
                                  case "B": state[i].B = state[i].B+1; break;
                                  case "C": state[i].C = state[i].C+1; break;
                              }
                              state[i].total = state[i].total+1
                          }
                          return state;
                      },
                      merge: function(state1, state2) {                 //merge function to merge the intermediate results
                          for(i=0;i<state.length;i++){
                              state[i].A = state1[i].A+state2[i].A
                              state[i].B = state1[i].B+state2[i].B
                              state[i].C = state1[i].C+state2[i].C
                              state[i].total = state1[i].total+state2[i].total
                              }
                          return state;
                      },
                      finalize: function(state){                        //finalize the result by calculating % ratio
                          for(i=0;i<state.length;i++){
                              state[i].A = state[i].A*100/state[i].total;
                              state[i].B = state[i].B*100/state[i].total;
                              state[i].C = state[i].C*100/state[i].total;
                              delete state[i].total;
                          }
                          return state;
                      },
                      lang: "js"
                  }
              }
          }
      }
      ]);
    < {
          "_id" : "xxx",
          "statistics" : [ 
              {
                  "question" : 1.0, "A" : 20.0, "B" : 80.0, "C" : 0.0
              }, 
              {
                  "question" : 2.0, "A" : 20.0, "B" : 60.0, "C" : 20.0
              }, 
              {
                  "question" : 3.0, "A" : 0.0, "B" : 20.0, "C" : 80.0
              }
          ]
      }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search