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

Question

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?

nimrod serok · Accepted Answer · 2022-11-03 13:31:30Z

2

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

edited Nov 3, 2022 at 13:31

answered Nov 3, 2022 at 13:08

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheStranger Over a year ago

I always want all 4 Array Elements returned. Even if it's 0, which it can very well be. And all documents have a count. But what If the ranges is like 1-4, 5-7, 7-(lt)8.5 and (gte)8.5-10. How do I divide it then? Check the post, I've updated the ranges.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related