How to sum and count in 3rd level nested array

Working with Data
1

I have a document, which contains an array of obj. each first level array contains lessonId and year and report. in the report array there are activityid, meeting, and score. in the score there are keys or types of lessons (daily grades, mid-semester assessments, END-SEMESTER ASSESSMENTS) and grades.

//
// Paste one or more documents here
[{
  "creatorId": null,
  "creatorName": null,
  "updateTime": null,
  "updaterId": null,
  "updaterName": null,
  "deleteTime": null,
  "deleterId": null,
  "deleterName": null,
  "isDelete": false,
  "companyId": {
    "$oid": "635c70892e8cfaf4a7d49a3f"
  },
  "memberId": {
    "$oid": "635d30b60c381f79913848a9"
  },
  "isActive": false,
  "name": "JOHN DOE",
  "profileImage": "string",
  "email": "jhanaway123@gmail.com",
  "phone": "0812345678",
  "mainClassId": {
    "$oid": "635d2e398e7b6138e9a65111"
  },
  "classId": {
    "$oid": "635d2f6e6804b95ce6a9e5d0"
  },
  "tags": [],
  "datas": [
    {
      "id": {
        "$oid": "635dd385f2435ea848fa33fe"
      },
      "classId": {
        "$oid": "6303479db2c68583135ec55f"
      },
      "lessonId": {
        "$oid": "635d2bf3a78013b9dcae9a6f"
      },
      "year": "2023",
      "report": [
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 3,
          "isPresent": true,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 70
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 4,
          "isPresent": false,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 70
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 6,
          "isPresent": false,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 80
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 8,
          "isPresent": true,
          "scores": [
            {
              "key": "PENILAIAAN TENGAH SEMESTER",
              "value": 80
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 9,
          "isPresent": true,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 50
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 12,
          "isPresent": true,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 90
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 13,
          "isPresent": true,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 100
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 14,
          "isPresent": true,
          "scores": [
            {
              "key": "NILAI HARIAN",
              "value": 85
            }
          ]
        },
        {
          "activityId": {
            "$oid": "6308350b119027c236c8ce92"
          },
          "meet": 15,
          "isPresent": true,
          "scores": [
            {
              "key": "PENILAIAAN AKHIR SEMESTER",
              "value": 73
            }
          ]
        }
      ]
    }
  ]
}
]

How to find sums for NILAI HARIAN, PENILAIAAN TENGAH SEMESTER and PENILAIAAN AKHIR SEMESTER in each lessonId.

i’am tring to aggregate and sum value from “NILAI HARIAN” didn’t work.
this is my sample code

2

Hi @Nuur_zakki_Zamani

Its fairly an easier Pipeline :

db.collection.aggregate([
  {
    "$match": {
      "companyId": ObjectId("635c70892e8cfaf4a7d49a3f")
    }
  },
  {
    "$unwind": {
      "path": "$datas"
    }
  },
  {
    "$unwind": {
      "path": "$datas.report"
    }
  },
  {
    "$unwind": {
      "path": "$datas.report.scores"
    }
  },
  {
    "$group": {
      _id: "$datas.report.scores.key",
      sum: {
        $sum: "$datas.report.scores.value"
      }
    }
  }
])

Thanks
Pavel

1 Like
3

alhamdulillah, tankyou sir @Pavel_Duchovny :saluting_face: :saluting_face: :saluting_face:

4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.