How to group on each document in array of documents

Question

I have following document structure:

{
  "_id": "5d7e29896b149c50b01c88de",
  "submitted": true,
  "test": {
    "questions": [
      {
        "answerOptions": [
          "a",
          "b",
          "c",
          "d"
        ],
        "correctAnswers": [
          "a"
        ],
        "tags": [
          "tag1"
        ],
        "_id": "5d4cafa089fa358bcfc90b1c",
        "question": "Some question text 2",
        "category": "cat1",
        "__v": 0,
        "attempted": true,
        "answer": [
          "a"
        ],
        "isCorrect": true,
        "timeSpent": 3.28
      },
      {
        "answerOptions": [
          "a1",
          "b1",
          "c1",
          "d1"
        ],
        "correctAnswers": [
          "b1"
        ],
        "tags": [
          "tag1"
        ],
        "_id": "5d4cafa089fa358bcfc90b1d",
        "question": "Some question text",
        "category": "cat2",
        "__v": 0,
        "attempted": true,
        "answer": [
          "b1"
        ],
        "isCorrect": false,
        "timeSpent": 3.28
      }
    ],
    "_id": "5d7297af73e90c0bbfbd9575",

    "sections": [
      {
        "_id": "5d7297af73e90c0bbfbd9577",
        "name": "cat1"
      },
      {
        "_id": "5d7297af73e90c0bbfbd9576",
        "name": "cat2"
      }
    ],
    "duration": 30,
    "sectionalDuration": false,
    "isPaid": false,
    "isPublished": true,
    "createdAt": 1567791023,
    "lastModified": 1567791023,
    "__v": 0
  },

  "user": "5d4c7e91c2e50e7353f6cc07",
  "testRef": "5d7297af73e90c0bbfbd9575",
  "instituteRef": "5d46da097a00bcc2d9471700"
}

Above mentioned doc represents a student test for institute id 5d46da097a00bcc2d9471700 and test id 5d7297af73e90c0bbfbd9575

What i am trying to achieve is : Get a List of top N institutes for a test by avg score. Where for each institute and for each test in that institute (one test is above mentioned doc). Get avg score by calculating total correct answers (i.e isCorrect = true) / total tests (doc count of that institute)

I want final answer like

[
  {"institute1":{avg:1}},
  {"institute2":{avg:2}},
]

I am not posting it here directly. I have tried creating multiple queries acc to my knowledge of aggregation .I am new to aggregatiom framework and I have tried searching online for almost 15 days now , got nothing related to this.

Answer 1

The following query can get us the expected output:

db.collection.aggregate([
    {
        $group:{
            "_id":"$instituteRef",
            "totalCorrect":{
                $sum:{
                    $size:{
                        $filter:{
                            "input":"$test.questions",
                            "as":"question",
                            "cond":{
                                $eq:["$$question.isCorrect",true]
                            }
                        }
                    }
                }
            },
            "totalTests":{
                $sum:1
            }
        }
    },
    {
        $project:{
            "_id":0,
            "institute":"$_id",
            "avg":{
                $divide:["$totalCorrect","$totalTests"]
            }
        }
    },
    {
        $sort:{
            "avg":-1
        }
    },
    {
        $limit:10
    }
]).pretty()

Data set:

{
  "_id" : "5d7e29896b149c50b01c88de",
  "submitted" : true,
  "test" : {
    "questions" : [
      {
        "answerOptions" : [
          "a",
          "b",
          "c",
          "d"
        ],
        "correctAnswers" : [
          "a"
        ],
        "tags" : [
          "tag1"
        ],
        "_id" : "5d4cafa089fa358bcfc90b1c",
        "question" : "Some question text 2",
        "category" : "cat1",
        "__v" : 0,
        "attempted" : true,
        "answer" : [
          "a"
        ],
        "isCorrect" : true,
        "timeSpent" : 3.28
      },
      {
        "answerOptions" : [
          "a1",
          "b1",
          "c1",
          "d1"
        ],
        "correctAnswers" : [
          "b1"
        ],
        "tags" : [
          "tag1"
        ],
        "_id" : "5d4cafa089fa358bcfc90b1d",
        "question" : "Some question text",
        "category" : "cat2",
        "__v" : 0,
        "attempted" : true,
        "answer" : [
          "b1"
        ],
        "isCorrect" : false,
        "timeSpent" : 3.28
      }
    ],
    "_id" : "5d7297af73e90c0bbfbd9575",
    "sections" : [
      {
        "_id" : "5d7297af73e90c0bbfbd9577",
        "name" : "cat1"
      },
      {
        "_id" : "5d7297af73e90c0bbfbd9576",
        "name" : "cat2"
      }
    ],
    "duration" : 30,
    "sectionalDuration" : false,
    "isPaid" : false,
    "isPublished" : true,
    "createdAt" : 1567791023,
    "lastModified" : 1567791023,
    "__v" : 0
  },
  "user" : "5d4c7e91c2e50e7353f6cc07",
  "testRef" : "5d7297af73e90c0bbfbd9575",
  "instituteRef" : "5d46da097a00bcc2d9471700"
}

Output:

{ "institute" : "5d46da097a00bcc2d9471700", "avg" : 1 }