CODEWITHSUNDEEP
databasemongodbmongooseaggregation-framework
Dijkie85
Dijkie85

Reputation: 1106

Mongoose return average of values grouped by fields in subdocument

I'm trying to build a kind of yelp clone using express/mongoose and need to be able to return an object containing the average score for each of the rating categories (for ex: global, location, food, and ambience), each from a different review subdocument. A sample Restaurant would look something like this (I've taken out irrelevant fields):

{ 
  name: 'Test Restaurant',
  reviews: [ 
    { 
      _id: 5e42c12e903eac188077dca5, 
      rating: {
        global: 10,
        location: 5,
        food: 8,
        ambient: 6
        } 
    },
    { 
      _id: 5e42c12e903eac188077dca6, 
      rating: {
        global: 5,
        location: 6,
        food: 4,
        ambient: 2
        } 
    },
    { 
      _id: 5e42c12e903eac188077dca7, 
      rating: {
        global: 9,
        location: 3,
        food: 5,
        ambient: 1
        } 
    },
  ]
}

So far I've been able to do this using the following virtual, but I imagine this rapidly becoming inefficient if the number of reviews grows big enough:

restaurantSchema.virtual("averageRatings").get(function() {
  const restaurant = this;
  const ratings = restaurant.reviews.map(review => review.rating);
  const ratingsArrays = {};
  const averageRatings = {};
  const categories = [
    "global",
    "food",
    "ambient",
    "location"
  ];

  //Loop for every category, create new array with the ratings from each review per category, and filter out null ratings
  categories.forEach(category => {
    ratingsArrays[category] = ratings
      .map(rating => rating[category])
      .filter(value => value != null);

  //Calculate average for each category, and return undefined where there are not ratings
    averageRatings[category] =
      ratingsArrays[category].reduce((acc, current) => acc + current, 0) /
        ratingsArrays[category].length || undefined;
  });
  return averageRatings;
});

Now, I've seen the more efficient/proper way of doing this is by using the MongoDB aggregation framework, but even though I've looked through the manual and found some very good answers for particular situations, I haven't been able to work through it in this particular case.

Could help me with an approximate pipeline I could work with? Do you know of any resources to learn MongoDB query/aggregation syntax in a friendlier way than the official manuals (Something like this for SQL: https://sqlbolt.com/)?

Upvotes: 1

Views: 337

Answers (1)

mickl
mickl

Reputation: 49945

Using Aggregation Framework you can calculate your averages dynamically reading rating keys. Try to start with $objectToArray and then group by retrieved keys (k). Ultimately you need to run reverse operation which is $arrayToObject:

db.collection.aggregate([
    {
        $unwind: "$reviews"
    },
    {
        $project: {
            values: { $objectToArray: "$reviews.rating" }
        }
    },
    {
        $unwind: "$values"
    },
    {
        $group: {
            _id: { _id: "$_id", k: "$values.k" },
            sum: { $sum: "$values.v" },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id._id",
            averages: { $push: { k: "$_id.k", v: { $divide: [ "$sum", "$count" ] } } }
        }
    },
    {
        $project: {
            avgReviews: { $arrayToObject: "$averages" }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions