CODEWITHSUNDEEP
mongodbaggregation-framework
helloworld
helloworld

Reputation: 1042

How to use aggregate for group by in mongodb

I have collection that contains documents with below schema.

Schema

{
    "categoryId": "1234",
    "sellerId": "2323",
    "productId": "121",
    "rating": 1
},
{
    "categoryId": "1235",
    "sellerId": "2323",
    "productId": "122",
    "rating": -1
},
{
    "categoryId": "1234",
    "sellerId": "2323",
    "productId": "123",
    "rating": -1
},
{
    "categoryId": "1235",
    "sellerId": "2323",
    "productId": "124",
    "rating": 1
},
{
    "categoryId": "1234",
    "sellerId": "2323",
    "productId": "125",
    "rating": 1
},
{
    "categoryId": "1234",
    "sellerId": "2325",
    "productId": "125",
    "rating": 1
}

The rating can have values 1 or -1. I want to find all documents grouped by categoryId and sum of the ratings. Example Result:

{categoryId: 1234, positiveRatingCount: 2, negativeRatingCount: 1}

This is what I have done so far:

ratingsCollection.aggregate(
    {
        $match: {sellerId: "2323" }
    },
    {

        $group: {
            _id: "$categoryId",
            count: { $sum: "rating" }

        }
    }
);

I get the following result. I am able to group by category but not able to figure out to get count of positive and negative ratings.

[
    {
        "_id": "1234",
        "count": 3
    },
    {
        "_id": "1235",
        "count": 2
    }
]

Upvotes: 1

Views: 65

Answers (1)

Ashh
Ashh

Reputation: 46491

You need to use $sum with the conditions($cond) where the rating is $gt or $lt then 0

db.collection.aggregate([
  { "$match": { "sellerId": "2323" } },
  { "$group": {
    "_id": "$categoryId",
    "positiveRatingCount": {
      "$sum": { "$cond": [{ "$gt": [ "$rating", 0 ] }, "$rating", 0 ] }
    },
    "negativeRatingCount": {
      "$sum": { "$cond": [{ "$lt": [ "$rating", 0 ] }, "$rating", 0 ] }
    }
  }}
])

Output

[
  {
    "_id": "1235",
    "negativeRatingCount": -1,
    "positiveRatingCount": 1
  },
  {
    "_id": "1234",
    "negativeRatingCount": -2,
    "positiveRatingCount": 3
  }
]

Upvotes: 2

Related Questions