Adding values not found when grouping for a rating system

[Nicholas_Tsaoucis]

I have an aggregate that I’ve built which does what i need it to do and counts how many of each rating (1-5) a user has been given. They can receive a rating from two distinct parties. which are presented and calculated separately then with a total avg as well.

(yes, I have probably over complicated it, but it works currently. Hopefully, it doesn’t run into issues in the future with size)

What I’d like to try and do is add a 0 count to the ratings not found.

this is an example of the output.
in supervisor_counts you can see the user recieved 53 ratings of 1 and 1 rating of 5.
Id like it to be able to also show 0 for the possible ratings of 2, 3 and 4.

{
  "_id": "string",
  "supervisor_rating_avg": 1.0740740740740742,
  "supervisor_rating_count": 58,
  "supervisor_counts": [
    {
      "rating": 5,
      "count": 1
    },
    {
      "rating": 1,
      "count": 53
    }
  ],
  "venue_manager_rating_avg": 1.9814814814814814,
  "venue_manager_rating_count": 107,
  "venue_manager_counts": [
    {
      "rating": 2,
      "count": 53
    },
    {
      "rating": 1,
      "count": 1
    }
  ],
  "totalAvg": 1.5277777777777777
}

This is the aggregate

[
  {
    '$unwind': {
      'path': '$tasks'
    }
  }, {
    '$facet': {
      'vmratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.venue_manager_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }, 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'vmcounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'sratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.supervisor_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'scounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'totalAvg': [
        {
          '$group': {
            '_id': '$tasks.resource.uuid', 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }, 
            'sratingavg': {
              '$avg': '$tasks.supervisor_assessment.rating'
            }, 
            'sratingcount': {
              '$sum': '$tasks.supervisor_assessment.rating'
            }, 
            'vmratingcount': {
              '$sum': '$tasks.venue_manager_assessment.rating'
            }
          }
        }
      ]
    }
  }, {
    '$project': {
      'result': {
        '$map': {
          'input': {
            '$range': [
              0, {
                '$size': '$vmratings'
              }
            ]
          }, 
          'as': 'x', 
          'in': {
            '$let': {
              'vars': {
                'id': {
                  '$arrayElemAt': [
                    '$vmratings._id', '$$x'
                  ]
                }
              }, 
              'in': {
                '_id': '$$id', 
                'vmratings': {
                  '$first': {
                    '$filter': {
                      'input': '$vmratings', 
                      'as': 'q', 
                      'cond': {
                        '$eq': [
                          '$$q._id', '$$id'
                        ]
                      }
                    }
                  }
                }, 
                'sratings': {
                  '$first': {
                    '$filter': {
                      'input': '$sratings', 
                      'as': 'q', 
                      'cond': {
                        '$eq': [
                          '$$q._id', '$$id'
                        ]
                      }
                    }
                  }
                }, 
                'totalAvg': {
                  '$first': {
                    '$filter': {
                      'input': '$totalAvg', 
                      'as': 'q', 
                      'cond': {
                        '$eq': [
                          '$$q._id', '$$id'
                        ]
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }, {
    '$unwind': {
      'path': '$result'
    }
  }, {
    '$replaceWith': {
      '$mergeObjects': [
        '$result.sratings', '$result.totalAvg', '$result.vmratings'
      ]
    }
  }, {
    '$project': {
      '_id': '$_id', 
      'supervisor_rating_avg': '$sratingavg', 
      'supervisor_rating_count': '$sratingcount', 
      'supervisor_counts': '$scounts', 
      'venue_manager_rating_avg': '$vmratingavg', 
      'venue_manager_rating_count': '$vmratingcount', 
      'venue_manager_counts': '$vmcounts', 
      'totalAvg': {
        '$divide': [
          {
            '$sum': [
              '$vmratingavg', '$sratingavg'
            ]
          }, 2
        ]
      }
    }
  }
]

any suggestions on simplifying are also welcome

[Nicholas_Tsaoucis]

I ended up simplifying it further. Its more steps but I think it will be more performant since it not making one huge doc in one stage anymore.

[
  {
    '$unwind': {
      'path': '$tasks'
    }
  }, {
    '$replaceRoot': {
      'newRoot': '$tasks'
    }
  }, {
    '$group': {
      '_id': '$resource.uuid', 
      'supervisor_assessments': {
        '$push': '$supervisor_assessment.rating'
      }, 
      'venue_manager_assessments': {
        '$push': '$venue_manager_assessment.rating'
      }
    }
  }, {
    '$project': {
      'ratings': {
        '$concatArrays': [
          '$supervisor_assessments', '$venue_manager_assessments'
        ]
      }
    }
  }, {
    '$unwind': {
      'path': '$ratings'
    }
  }, {
    '$group': {
      '_id': {
        'id': '$_id', 
        'ratingVal': '$ratings'
      }, 
      'count': {
        '$count': {}
      }
    }
  }, {
    '$group': {
      '_id': '$_id.id', 
      'ratings': {
        '$push': {
          'k': {
            '$toString': '$_id.ratingVal'
          }, 
          'v': '$count'
        }
      }
    }
  }, {
    '$project': {
      'ratings': {
        '$arrayToObject': '$ratings'
      }, 
      'total': {
        '$sum': '$ratings.v'
      }
    }
  }, {
    '$project': {
      '1Star': {
        '$sum': {
          '$ifNull': [
            '$ratings.1', 0
          ]
        }
      }, 
      '2Star': {
        '$sum': {
          '$ifNull': [
            '$ratings.2', 0
          ]
        }
      }, 
      '3Star': {
        '$sum': {
          '$ifNull': [
            '$ratings.3', 0
          ]
        }
      }, 
      '4Star': {
        '$sum': {
          '$ifNull': [
            '$ratings.4', 0
          ]
        }
      }, 
      '5Star': {
        '$sum': {
          '$ifNull': [
            '$ratings.5', 0
          ]
        }
      }, 
      'total': '$total', 
      'avg': {
        '$divide': [
          {
            '$sum': [
              '$ratings.1', {
                '$multiply': [
                  '$ratings.2', 2
                ]
              }, {
                '$multiply': [
                  '$ratings.3', 3
                ]
              }, {
                '$multiply': [
                  '$ratings.4', 4
                ]
              }, {
                '$multiply': [
                  '$ratings.5', 5
                ]
              }
            ]
          }, '$total'
        ]
      }
    }
  }
]

[Nicholas_Tsaoucis]

This gives me return like this

{
_id: "string"
1Star: 54
2Star: 53
3Star: 0
4Star: 0
5Star: 1
total: 108
avg: 1.5277777777777777
}