Home > Enterprise >  MongoDB - How to sum all subject marks
MongoDB - How to sum all subject marks

Time:11-12

I want to sum the marks of all subjects from the studentMarkDetails array. How can I do this one? It was in the studentMarkDetails array if I use $unwind I am getting like 0 records fetch.

I need output like this:

{
    "_id": ObjectId("636efe231eeef2f46a31d7f4"),
    "sName": "Somu",
    "class": "tenth",
    "year": 2003,
    "examType": "quaterly",
    "total_marks": 300
}

My document is:

{
    "_id": ObjectId("636efe231eeef2f46a31d7f4"),
    "sName": "Somu",
    "class": "tenth",
    "year": 2003,
    "studentMarkDetails": [ 
        {
            "examType": "quaterly",
            "marks": {
                "Eng": 55,
                "Tel": 45,
                "Mat": 75,
                "Sec": 43,
                "Soc": 65
            }
        }, 
        {
            "examType": "halfyearly",
            "marks": {
                "Eng": 56,
                "Tel": 76,
                "Mat": 89,
                "Sec": 34,
                "Soc": 76
            }
        }, 
        {
            "examType": "final",
            "marks": {
                "Eng": 89,
                "Tel": 78,
                "Mat": 91,
                "Sec": 95,
                "Soc": 87
            }
        }
    ]
}

CodePudding user response:

There are a few ways to achieve the expected output. The key to the solution is you need to convert the marks object to an array via $objectToArray, then perform the sum.

Solution 1

  1. $project -

    1.1. $sum - Sum the array value from the result 1.1.1.

    1.1.1. $map - Iterate each element in an array and result in a new array.

    1.1.1.1. input - Convert the marks object into an object array (form: { k: "Eng", v: 89 }).

    1.1.1.2. in - Return v.

db.collection.aggregate([
  {
    $unwind: "$studentMarkDetails"
  },
  {
    $project: {
      "sName": 1,
      "class": 1,
      "year": 1,
      "examType": "$studentMarkDetails.examType",
      "total_marks": {
        $sum: {
          $map: {
            input: {
              $objectToArray: "$studentMarkDetails.marks"
            },
            in: "$$this.v"
          }
        }
      }
    }
  }
])

Demo Solution 1 @ Mongo Playground


Solution 2

You may also work with $reduce which aims to transform an array into a new value. The result will be the same as Solution 1.

db.collection.aggregate([
  {
    $unwind: "$studentMarkDetails"
  },
  {
    $project: {
      "sName": 1,
      "class": 1,
      "year": 1,
      "examType": "$studentMarkDetails.examType",
      "total_marks": {
        $reduce: {
          input: {
            $objectToArray: "$studentMarkDetails.marks"
          },
          initialValue: 0,
          in: {
            $sum: [
              "$$value",
              "$$this.v"
            ]
          }
        }
      }
    }
  }
])

Demo Solution 2 @ Mongo Playground

  • Related