Home > other >  Group by months and get their counts in MongoDB
Group by months and get their counts in MongoDB

Time:12-07

I have a document with the following structure.

{
    "_id" : ObjectId(""),
    "review_id" : "1",
    "product_id" : "1",
    "date" : 1638869377,
    "rating" : 5,
    "title" : "lorem",
    "review" : "lorem",
    "updated_at" : ISODate("2021-12-07T07:10:55.732Z"),
    "created_at" : ISODate("2021-12-07T05:04:11.750Z")
}

I managed to get the number of user comments by month. But I want to get it separately for each rating.

For example, in the 10th month there are 7 comments and how can I get 2 of them 1 star, 3 of them 4 stars, 2 of them 5 stars?

 {
  "$group": {
    "_id": {
      "month": {
        "$month": {
          "$toDate": "$date"
        }
      }
    },
    "reviews": {
      "$sum": 1
    }
  }
}

CodePudding user response:

You just want to $group by both fields, the month and the rating, like so:

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "month": {
          "$month": {
            "$toDate": "$date"
          }
        },
        rating: "$rating"
      },
      "reviews": {
        "$sum": 1
      }
    }
  }
])

Mongo Playground

Or my recommendation just to add a little more structuring to the output is to use this pipeline.

CodePudding user response:

You could add everything you want to group by inside the _id field. Just adding a new field with the rating would do the job.

{
  "$group": {
    "_id": {
      "month": {
        "$month": {
          "$toDate": "$date"
        }
      },
      "rating": "$rating"
    },
    "reviews": {
      "$sum": 1
    }
  }
}

Note: I would also add a group by year, so you can get each month separed properly. If you dont add them, Mongo will group every January of every year together for example.

Playground

  • Related