Question 116444 in Mongodb Question posted in
The official documentation can be found here.

Query to count each selected answer of question from array of forms with mongodb

I’m new to using MongoDB queries.

I would like to count the number of selected answers based on the question.

Here is an example of data I’m trying to process:

  {
    "_id": "910ef391-bc06-4fda-80c5-c6a6d9bcXXXX",
    "book": {
      "register": [
        {
          "questions": [
            {
              "id": "87b8c60c-9c9e-41d1-a52a-588400deXXXX",
              "name": "Question A",
              "answers": [
                {
                  "_id": 1,
                  "label": "Answer 1",
                  "selected": true
                },
                {
                  "_id": 2,
                  "label": "Answer 2",
                  "selected": false
                }
              ]
            },
            {
              "id": "55b8c60c-9c9e-41d1-a52a-588400deXXXX",
              "name": "Question B",
              "answers": [
                {
                  "_id": 1,
                  "label": "Answer 1",
                  "selected": true
                },
                {
                  "_id": 2,
                  "label": "Answer 2",
                  "selected": false
                }
              ]
            }
          ]
        }
      ]
    }
  },
  {
    "_id": "120ef391-bc06-4fda-80c5-c6a6d9bcXXXX",
    "book": {
      "register": [
        {
          "questions": [
            {
              "id": "99b8c60c-9c9e-41d1-a52a-588400deXXXX",
              "name": "Question A",
              "answers": [
                {
                  "_id": 1,
                  "label": "Answer 1",
                  "selected": true
                },
                {
                  "_id": 2,
                  "label": "Answer 2",
                  "selected": false
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

Here is the expected result:

[
  {
    "Answer 1": 2
  }
]

I would like to count the number of selected answers to question A.

I’ve tried something, but it returns no document found

db.collection.aggregate([
  {
    $group: {
      _id: "$book.register.questions",
      questions: {
        $push: "$book.register.questions"
      }
    }
  },
  {
    $match: {
      "questions.name": "Question A"
    }
  }
])

Thank you for your help !🤙

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It works !!! Just move the first $match pipeline after the $unwind pipeline: "$book.register.questions". @Yong Shun => Thank you very much for your answer, your reactivity and your quality of response !

    db.collection.aggregate([
  {
    $unwind: "$book.register"
  },
  {
    $unwind: "$book.register.questions"
  },
  {
    $match: {
      "book.register.questions.name": "Question A"
    }
  },
  {
    $unwind: "$book.register.questions.answers"
  },
  {
    $group: {
      _id: "$book.register.questions.answers.label",
      count: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$book.register.questions.answers.selected",
                true
              ]
            },
            then: 1,
            else: 0
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      answers: {
        $push: {
          k: "$_id",
          v: "$count"
        }
      }
    }
  },
  {
    $replaceWith: {
      $arrayToObject: "$answers"
    }
  }
])

  2. 0

    A bit long query.

    1. $match – Filter the document with dot notation to find the document containing "Question A".

    2. $unwind – Deconstruct book.register array into multiple documents.

    3. $unwind – Deconstruct book.register.questions array into multiple documents.

    4. $unwind – Deconstruct book.register.questions.answers array into multiple documents.

    5. $group – Group by book.register.questions.answers.label and perform conditional total sum based on book.register.questions.answers.selected value. Add 1 if it is true.

    6. $group – Group by null. This aims to combine all the documents into a single document by pushing the answer label and count into the answers array.

    7. $replaceWith – Replace the input document to convert from the answers array into key-value pair.

    db.collection.aggregate([
  {
    $match: {
      "book.register.questions.name": "Question A"
    }
  },
  {
    $unwind: "$book.register"
  },
  {
    $unwind: "$book.register.questions"
  },
  {
    $unwind: "$book.register.questions.answers"
  },
  {
    $group: {
      _id: "$book.register.questions.answers.label",
      count: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$book.register.questions.answers.selected",
                true
              ]
            },
            then: 1,
            else: 0
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      answers: {
        $push: {
          k: "$_id",
          v: "$count"
        }
      }
    }
  },
  {
    $replaceWith: {
      $arrayToObject: "$answers"
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search