How do I group and count values by value range in MongoDB-CodePudding

I have the following documents in my MongoDB:

_id: ObjectId(...)
'timestamp': 2022-11-03T10:00:00.000 00:00
score: 1

_id: ObjectId(...)
'timestamp': 2022-11-03T09:00:00.000 00:00
score: 3

_id: ObjectId(...)
'timestamp': 2022-11-03T10:00:00.000 00:00
score: 6

_id: ObjectId(...)
'timestamp': 2022-11-03T10:00:00.000 00:00
score: 10

I want to make an aggregation that counts the score within the range of (gte)1-(lt)5 as poor, (gte)5-(lt)7 as ok, (gte)7-(lt)8.5 as good and (gte)8.5-(lte)10 as excellent.

So the result would look like this:

{
    "data": [
        {
            "name": "excellent",
            "count": 1
        },
        {
            "name": "good",
            "count": 0
        },
        {
            "name": "ok",
            "count": 1
        },
        {
            "name": "poor",
            "count": 2
        }
    ]
}

How do I achieve that?

CodePudding user response：

If you accept an answer only with documents that have a count, you can do:

db.collection.aggregate([
  {$project: {
      _id: {
        $arrayElemAt: [
          ["poor", "ok", "good", "excellent"], 
          {$floor: {$divide: ["$score", 10]}}
        ]}
  }},
  {$group: {_id: "$_id", count: {$sum: 1}}}
])

Otherwise you need to create all categories:

db.collection.aggregate([
  {$group: {
      _id: 0,
      excellent: {$sum: {$cond: [{$gte: ["$score", 30]}, 1, 0]}},
      good: {$sum: {$cond: [{$and: [{$gte: ["$score", 20]}, {$lt: ["$score", 30]}]}, 1, 0]}},
      ok: {$sum: {$cond: [{$and: [{$gte: ["$score", 10]}, {$lt: ["$score", 20]}]}, 1, 0]}},
      poor: {$sum: {$cond: [{$lt: ["$score", 10]}, 1, 0]}} 
  }},
  {$unset: "_id"},
  {$project: {data: {$objectToArray: "$$ROOT"}}},
  {$project: {
      data: {$map: {
          input: "$data",
          in: {nmae: "$$this.k", count: "$$this.v"}
      }}
  }}
])

See how it works on the playground example