0

I have mongo collection with survey answers submitted by each user. I would like to get the count of users selected as an option. Only one user has selected the option O12. The output should be 1.

{
    "_id" : ObjectId("5ea179eb39ff117948f19266"),
    "_class" : "model.survey.Answer",
    "survey_id" : "5ea178c239ff117948f19265",
    "survey_user" : [ 
        {
            "user_id" : 1072,
            "user_option" : [ 
                {
                    "question_id" : "Q1",
                    "option_id" : "O11"
                }, 
                {
                    "question_id" : "Q2",
                    "option_id" : "O21"
                }, 
                {
                    "question_id" : "Q3",
                    "option_id" : "O31"
                }, 
                {
                    "question_id" : "Q4",
                    "option_id" : "O41"
                }
            ]
        }, 
        {
            "user_id" : 1073,
            "user_option" : [ 
                {
                    "question_id" : "Q1",
                    "option_id" : "O12"
                }, 
                {
                    "question_id" : "Q2",
                    "option_id" : "O21"
                }, 
                {
                    "question_id" : "Q3",
                    "option_id" : "O31"
                }, 
                {
                    "question_id" : "Q4",
                    "option_id" : "O41"
                }
            ]
        }
    ]
}
0

1 Answer 1

2

You can do that using MongoDB's aggregation-pipeline :

Different ways to do it, One way is to use $unwind:

Type 1 - Query 1 :

db.collection.aggregate([
    /** Optional but will be good on huge collections to lessen data for further stages */
    {
      $match: { "survey_user.user_option.option_id": "O12" }
    },
    {
      $unwind: "$survey_user"
    },
    /** When you unwind a each object/element in array gets it's own document after `unwind` stage */
    {
      $match: { "survey_user.user_option.option_id": "O12" }
    },
    /** After match you'll only have objects which met the criteria in `survey_user` array */
    /** group on `_id` & push entire original doc to data field */
    {
      $group: { _id: "$_id", survey_user: { $push: "$survey_user" }, data: {  $first: "$$ROOT" } }
    },
    /** Add `survey_user` array to `data.survey_user` & it's size to `data.optedCount` field */
    {
      $addFields: { "data.survey_user": "$survey_user", "data.optedCount": {  $size: "$survey_user" } }
    },
    /** Make `data` as new root to doc */
    {
      $replaceRoot: { newRoot: "$data" }
    }
  ])

Test : mongoplayground

Just in case if you just need count but not needed the entire doc to be returned there will be a minor change in above query :

Type 1 - Query 2 :

db.collection.aggregate([
    {
      $match: { "survey_user.user_option.option_id": "O12" }
    },
    {
      $unwind: "$survey_user"
    },
    {
      $match: { "survey_user.user_option.option_id": "O12" }
    },
    /** Just group on `_id` & count no.of docs, maintain `survey_id` */
    {
      $group: { _id: "$_id", optedCount: { $sum: 1 }, survey_id: { $first: "$survey_id" } }
    }
  ])

Test : mongoplayground

Using array iterator $reduce, which might be helpful if your collections data is so huge, as unwind will explode your docs.

Type 2 - Query :

db.collection.aggregate([
  {
    $match: {
      "survey_user.user_option.option_id": "O12",
    },
  },
  /** Instead of `$addFields`, you can use `$project` to project fewer needed fields (which can be help improve query with performance benefits ) */
  {
    $addFields: {
      optedCount: {
        $reduce: {
          input: "$survey_user",
          initialValue: 0,
          in: {
            $cond: [
              { $in: ["O12", "$$this.user_option.option_id"] },
              { $add: ["$$value", 1] },
              "$$value",
            ]
          }
        }
      }
    }
  }
]);

Test : mongoplayground

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

2 Comments

what is $$ROOT in type 1 query 1
@NihalChandwani: It's a MongoDB's system variable to get the current top-level document. Read these for more info: stackoverflow.com/questions/61804268/… (OR) mongodb.com/docs/manual/reference/aggregation-variables/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.