Group books and get count for each of their ratings

Question

I have a Rating model with a book and rating value to it. I would like to get all the ratings count (ratings vary from 1 to 5) for each book in the database.

My schema simply looks like -

    {
      "_id": ObjectId("57e112312a52fe257e5d1d5c"),
      "book": ObjectId("57e111142a52fe257e5d1d42"),
      "rating": 4
    }
    {
      "_id": ObjectId("57e7a002420d22d6106a4715"),
      "book": ObjectId("57e111142a52fe257e5d1d42"),
      "rating": 5
    }
    {
      "_id": ObjectId("57e7a4cd98bfdb5a11962d54"),
      "book": ObjectId("57e111142a52fe257e5d17676"),
      "rating": 5
    }
    {
      "_id": ObjectId("57e7a4cd98bfdb5a11962d54"),
      "book": ObjectId("57e111142a52fe257e5d17676"),
      "rating": 1
    }

Currently, i have only been able to get to this point where i can get the no of ratings for each book but it doesn't specify exactly the rating value count.

This is my current query -

    db.ratings.aggregate([
        {$match: {book: {$in: [ObjectId("57e111142a52fe257e5d1d42"), ObjectId('57e6bef7cad79fa38555c643')]}}},
        {$group: {_id: {book: "$book", value: "$value"} } },
        {$group: {_id: "$_id.book", total: {$sum: 1}}},
    ])

The output is this -

    {
        "result": [
            {
                "_id": ObjectId("57e6bef7cad79fa38555c643"),
                "total": 2
            },
            {
                "_id": ObjectId("57e111142a52fe257e5d1d42"),
                "total": 2
            }
        ],
        "ok": 1
    }

However, i want to club all the documents and get a result with the count of ratings for each value of the rating field, something like below. The whole point is that i just want the count of ratings for each value for each book.

    {
        result: [
            {
                _id: "57e111142a52fe257e5d17676",
                5_star_ratings: 1,
                4_star_ratings: 3,
                3_star_ratings: 4,
                2_star_ratings: 1,
                1_star_ratings: 0,
            },
            {
                _id: "57e111142a52fe257e5d1d42",
                5_star_ratings: 10,
                4_star_ratings: 13,
                3_star_ratings: 7,
                2_star_ratings: 8,
                1_star_ratings: 19,
            }
            .
            .
            .
            .
        ]
    }

How do i go about this?

chridam · Accepted Answer · 2016-09-25T18:33:58.270

Accomplishing the task require a $group pipeline that uses the $cond operator in the $sum accumulator operator. The $cond operator will evaluate a logical condition based on its first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). This converts the true/false logic into 1 and 0 numerical values that feed into $sum respectively:

{
    "$sum": { 
        "$cond": [ { "$eq": [ "$rating", 1 ] }, 1, 0 ]
    }
}

As a resulting operation, you might want to run the following aggregation pipeline:

var pipeline = [
    { 
        "$match": {
            "book": {
                "$in": [
                    ObjectId("57e111142a52fe257e5d1d42"), 
                    ObjectId('57e6bef7cad79fa38555c643')
                ]
            }
        }
    },
    { 
        "$group": { 
            "_id": "$book",             
            "5_star_ratings": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$rating", 5 ] }, 1, 0 ]
                }
            },
            "4_star_ratings": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$rating", 4 ] }, 1, 0 ]
                }
            },
            "3_star_ratings": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$rating", 3 ] }, 1, 0 ]
                }
            },
            "2_star_ratings": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$rating", 2 ] }, 1, 0 ]
                }
            },
            "1_star_ratings": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$rating", 1 ] }, 1, 0 ]
                }
            }           
        }  
    },
]

db.ratings.aggregate(pipeline)

For a more flexible and better performant approach which executes much faster than the above, consider running an alternative pipeline as follows

db.ratings.aggregate([
    { 
        "$match": {
            "book": {
                "$in": [
                    ObjectId("57e111142a52fe257e5d1d42"), 
                    ObjectId('57e6bef7cad79fa38555c643')
                ]
            }
        }
    },
    { 
        "$group": {
            "_id": { 
                "book": "$name",
                "rating": "$rating"
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.book",
            "counts": {
                "$push": {
                    "rating": "$_id.rating",
                    "count": "$count"
                }
            }
        }
    }
])

oh my god, that worked like a charm, thank you so very much. Do you think this is a heavy operation and if there is a room for optimisation? — Kaushik Thirthappa, Sep 25 '16 at 18:22
@ktkaushik I would like to think there is room for improvement. Consider another approach that does not use the hashed key approach as an alternative. Will update my answer with that approach in a bit. — chridam, Sep 25 '16 at 18:26
Yeah, i mean that approach can be knocked off from the requirement and that's not a problem — Kaushik Thirthappa, Sep 26 '16 at 06:05
How can I add I next stage to get the average rating for a book @chridam? — O'Dane Brissett, Feb 27 '20 at 04:01
@O'DaneBrissett That's a good question, I would suggest you create a new post with that and I am more than happy to answer — chridam, Feb 27 '20 at 07:26
I posted the question. Here's the link https://stackoverflow.com/questions/60440813/mongoose-aggregate-to-get-average-rating-count-each-rate-and-return-the-actual @chridam — O'Dane Brissett, Feb 27 '20 at 19:52

Group books and get count for each of their ratings

1 Answers1