0

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?

Matt Price
  • 1,371
  • 2
  • 9
  • 19

1 Answers1

0

Query1

  • map on modules
  • and add a field on each with the avg progress of the videos

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

  • unwind
  • replace root to make structure better
  • add the avg field

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]}}}}}}])
Takis
  • 8,314
  • 2
  • 14
  • 25
  • this is awesome - and close. Rather than an average, however, if a video is not 100% completed it needs to be classed as 0. So, the second module for example would be 2/3 complete, so 66.6%. How would I do this? – Matt Price Dec 04 '21 at 16:47
  • i updated it, with one extra map before avg, you could also do a reduce and sum divide, but this works also, just query got a bit bigger – Takis Dec 04 '21 at 17:19
  • 1
    Amazing. Learnt a lot from this. Thanks. – Matt Price Dec 04 '21 at 18:30