Mongoose aggregate to get Average rating, count each rate and return the actual ratings

user_11029876 0
Viewed 1075

Im trying to to get avg rating for a product, plus the count of each rating and also return the actual ratings and use pagination to limit amount that is returned without affecting the avg or count.

So I'm trying achieve something like this:

this is my rating collection: 

       {
            "productId": "3"
            "userid" : 5,
            "rating" : 5
            "comment": "this is nice"
        },
        {
            "productId": "3"
            "userid" : 2,
            "rating" :4
            "comment": "this is very nice"
        }

and this is the end result I want

{
    "_id" : 1,
    "avgRating": "3.6"
    "counts" : [
        {
            "rating" : 5,
            "count" : 8
        },
        {
            "rating" : 3,
            "count" : 2
        },
        {
            "rating" : 4,
            "count" : 4
        },
        {
            "rating" : 1,
            "count" : 4
        }
    ],
   "ratings": [
       {
            "productId": "3"
            "userid" : 5,
            "rating" : 5
            "comment": "this is nice"
        },
        {
            "productId": "3"
            "userid" : 2,
            "rating" :4
            "comment": "this is very nice"
        },
        {
            "productId": "3"
            "userid" : 12,
            "rating" : 4
            "comment": "this is okay"
        }
    ]
}

I have this so far which give me the count for each rating: 

db.votes.aggregate([
    { $match: { postId: {$in: [1,2]} } },
    {
      $group: { _id: { post: "$postId", rating: "$vote" }, count: { $sum: 1 } }
    },
    {
      $group: {
        _id: "$_id.post",
        counts: { $push: { rating: "$_id.rating", count: "$count" } }
      }
    }
  ])
mongoose node.js mongodb
1 Answers

You're not far off, we just have to adjust some things:

db.votes.aggregate([
    {
        $match:
            {
                postId: {$in: [1, 2]}
            }
    },
    {
        $group: {
            _id: {post: "$postId", rating: "$vote"},
            count: {$sum: 1},
            reviews: {$push : "$$ROOT" } //keep the original document
        }
    },
    {
        $group: {
            _id: "$_id.post",
            counts: {$push: {rating: "$_id.rating", count: "$count"}},
            reviews: {$push: "$reviews"},
            totalItemCount: {$sum: "$count"}, //for avg calculation
            totalRating: {$sum: "$_id.rating"} // //for avg calculation
        }
    },
    {
        $project: {
            _id: "$_id",
            avgRating: {$divide: ["$totalRating", "$totalItemCount"]},
            counts: "$counts",
            reviews: {
                $slice: [
                    {
                        $reduce: {
                            input: "$reviews",
                            initialValue: [],
                            in: { $concatArrays: ["$$value", "$$this"] }
                        }
                    },
                    0, //skip
                    10 //limit
                ]
            }

        }
    }
])

Note that I preserved the current pipeline structure for clarity, however I feel that using a pipeline that utilizes $facet might be more efficient as we won't have to hold the entire collection in memory while grouping. we'll split it into two, one the current pipeline minus the review section and one with just $skip and $limit stages.

EDIT: $facet version:

db.votes.aggregate([
    {
        "$match": {
            "postId": {"$in": [1, 2]}
        }
    },
    {
        "$facet": {
            "numbers": [
                {
                    "$group": {
                        "_id": {
                            "post": "$postId",
                            "rating": "$vote"
                        },
                        "count": {
                            "$sum": 1.0
                        }
                    }
                },
                {
                    "$group": {
                        "_id": "$_id.post",
                        "counts": {
                            "$push": {
                                "rating": "$_id.rating",
                                "count": "$count"
                            }
                        },
                        "totalItemCount": {
                            "$sum": "$count"
                        },
                        "totalRating": {
                            "$sum": "$_id.rating"
                        }
                    }
                }
            ],
            "reviews": [
                {
                    "$skip": 0.0
                },
                {
                    "$limit": 10.0
                }
            ]
        }
    },
    {
        "$unwind": "$numbers"
    },
    {
        "$project": {
            "_id": "$numbers._id",
            "reviews": "$reviews",
            "avgRating": {"$divide": ["$numbers.totalRating", "$numbers.totalItemCount"]},
            "counts": "$numbers.counts"
        }
    }
]);
user_107703700
Related