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?

Answer 1

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"
                }
            }
        }
    }
])