javascriptmongodbmongodb-nodejs-driver

MongoDB aggregate to extract values from a nested array and return a total


I have this collection structure:

[
   "_id": "61a013b59f9dd0ebfd23ftgb",
   "modules": [
     {
       "_id": "61a013b59f9dd0ebfd964dgh",
       "videos": [
         {
           "_id": "213412",
           "progress": 100
         },
         {
           "_id": "61a013b59f9dd0ebfd965f4a",
           "progress": 0
         },
       ]
     },
     {
       "_id": "43556hujferwdhgsdft",
       "videos": [
         {
           "_id": "fdsg3sg98er989890",
           "progress": 66
         },
         {
           "_id": "fdsg3sg98er989890",
           "progress": 100
         },
         {
           "_id": "fdsg3sg98er989890",
           "progress": 100
         }
       ]
     }
   ]
 ]

I am trying to return the overall progress for each "module" by adding up all the videos that have progress of 100 and creating a percentage based on number of videos in the module. For example, the first module should return "module_progess" of 50 within it as this has 1/2 videos completed.

{
   "_id": "61a013b59f9dd0ebfd964dgh",
   "module_progress": 50,
   "videos": [
     {
       "_id": "213412",
       "progress": 100
     },
     {
       "_id": "61a013b59f9dd0ebfd965f4a",
       "progress": 0
     },
   ]
},

How do i access each videos object to make this calculation and add the new field to the response?


Solution

  • Query1

    Test code here

    aggregate(
    [{"$set": 
       {"modules": 
         {"$map": 
           {"input": "$modules",
            "in": 
            {"$mergeObjects": 
              ["$$this",
               {"module_progress": 
                 {"$avg": 
                   {"$map": 
                     {"input": "$$this.videos.progress",
                      "in": 
                      {"$cond": [{"$eq": ["$$progress", 100]}, "$$progress", 0]},
                      "as": "progress"}}}}]}}}}}])
    

    Query2

    Test code here

    aggregate(
    [{"$unwind": {"path": "$modules"}},
      {"$replaceRoot": {"newRoot": "$modules"}},
      {"$set": 
        {"module_progress": 
          {"$avg": 
            {"$map": 
              {"input": "$videos.progress",
                "in": {"$cond": [{"$eq": ["$$this", 100]}, "$$this", 0]}}}}}}])