I have a collection in mongodb, called recipe, where I have a document, called comments, which is an array, and in each recipe is saving the comments. Inside the comments array I have a ratings, which type is Number. So I want to calculate the average the ratings, but don't know, how can I use the db.collection().aggregate code to work in the recipe collection, in the comments document with the ratings variable.

Here is the recipe collection in mongodb:

const { Double } = require('bson');
const { timeStamp } = require('console');
const mongoose = require('mongoose');

const recipeSchema = new mongoose.Schema({
    name: {
        type: String,
        required: 'This field is required.'
    },
    description: {
        type: String,
        required: 'This field is required.'
    },
    quantity: {
        type: Array,
        required: 'This field is required.'
    },
    ingredients: {
        type: Array,
        required: 'This field is required.'
    },
    categoryByServing: {
        type: String,
        enum: ['Reggeli', 'Ebéd', 'Vacsora', 'Desszert', 'Levesek', 'Egyéb'],
        required: 'This field is required.'
    },
    categoryByNationality: {
        type: String,
        enum: ['Thai', 'Kínai', 'Indiai', 'Olasz', 'Angol', 'Magyar', 'Egyéb'],
        required: 'This field is required.'
    },
    image: {
        type: Array,
        required: 'This field is required.'
    },
    comments: [
        {
            username: String,
            comment: String,
            date: {
                type: Date,
                default: Date.now
            },
            rating: Number
        },{
            timestamps: true
        }
    ],
    count: {
        type: Number
    },
    likes: {
        type: Number
    },
    recipe_id: {
        type: String
    }

});

recipeSchema.index({ name: 'text', description: 'text' });
const Recipe = module.exports = mongoose.model('Recipe', recipeSchema);

Here is the code, where I implemented the rating avg calculation, which is inside the commenting post method:

 /**
 * POST /comment-recipe
 * Comment Recipe
*/
module.exports.CommentRecipeOnPost = async(req, res) => {
    
    let recipeId = req.params.id

    const comment = new Comment({
      username: req.body.username,
      comment: req.body.comment,
      date: req.body.date,
      rating: req.body.rating
    });
    comment.save((err, result) => {
      if (err){
        console.log(err)
      }else {
        Recipe.findById(req.params.id, (err, post) =>{
          if(err){
            console.log(err);
          }else{
            post.comments.push(result);
            post.save();




            db.collection('recipes').aggregate([
              {
                $unwind: "$comments"
              },
              {
                $group: {
                  _id: null,
                  avgrating: {
                    $avg: "$rating"
                  }
                }
              }
            ]).toArray()
              .then(results => {
                  console.log({ rating: results[0].avgrating })
              })
              .catch(error => console.error(error))





            console.log('====comments=====')
            console.log(post.comments);
            res.redirect('/recipe/'   recipeId);
          }
        })
      }
    })
}

CodePudding user response：

1. You have done the $unwind step correctly and now you will get a record for each comment.

  {
    "_id": "1",
    "comments": {
      "comment": "commment1-1",
      "rating": 4
    },
    "name": "recipe 1"
  },
  {
    "_id": "1",
    "comments": {
      "comment": "comment1-2",
      "rating": 3
    },
    "name": "recipe 1"
  },
...

2. In the $group stage group by something unique like the _id or the name and the $avg should be of $comments.rating instead of $rating.

In the end the pipeline should look something like this. demo

db.collection.aggregate([
  {
    $unwind: "$comments"
  },
  {
    $group: {
      _id: "$name", //group by something unique for that document containing comments
      avgRating: {
        $avg: "$comments.rating"
      }
    }
  }
])