Mongodb group by values and count the number of occurence

Question

I am trying to count how many times does a particular value occur in a collection.

{
  _id:1,
  field1: value,
  field2: A,
}

{
  _id:2,
  field1: value,
  field2: A,
}

{
  _id:3,
  field1: value,
  field2: C,
}

{
  _id:4,
  field1: value,
  field2: B,
}

what I want is to count how many times A occurs, B occurs and C occurs and return the count.

The output I want

{
  A: 2,
  B: 1,
  C: 1,
}

Hi, can you add example documents and expected output? – NeNaD Apr 07 '22 at 13:34 — NeNaD, Apr 07 '22 at 13:34
@NeNaD Please check the question again. – Apr 07 '22 at 13:39 — , Apr 07 '22 at 13:39

score 4 · Answer 1 · answered Apr 07 '22 at 14:04

4

This is typical use case for $group stage in Aggregation Pipeline. You can do it like this:

$group - to group all the documents by field2
$sum - to count the number of documents for each value of field2

db.collection.aggregate([
  {
    "$group": {
      "_id": "$field2",
      "count": {
        "$sum": 1
      }
    }
  }
])

Working example

answered Apr 07 '22 at 14:04

NeNaD

18,172
8
47
89

I tried the same thing before posting the question here but the solution provided above by J.F. is correct (the solution I want). Thanks for answering NeNaD – Apr 07 '22 at 16:08

score 1 · Accepted Answer · answered Apr 07 '22 at 13:41

You can use $facet in an aggregate pipeline like this:

$facet create "three ways" where in each one filter the values by desired key (A, B or C).
Then in a $project stage you can get the $size of the matched values.

db.collection.aggregate([
  {
    "$facet": {
      "first": [
        {
          "$match": {
            "field2": "A"
          }
        }
      ],
      "second": [
        {
          "$match": {
            "field2": "B"
          }
        }
      ],
      "third": [
        {
          "$match": {
            "field2": "C"
          }
        }
      ]
    }
  },
  {
    "$project": {
      "A": {
        "$size": "$first"
      },
      "B": {
        "$size": "$second"
      },
      "C": {
        "$size": "$third"
      }
    }
  }
])

Example here

Hi, I think this can be done much more efficient with `$group` stage than 3 separate `$facet`. :D — NeNaD, Apr 07 '22 at 14:10

chridam · Answer 3 · 2022-04-07T15:01:11.243

Leverage the $arrayToObject operator and a final $replaceWith pipeline to get the desired result. You would need to run the following aggregate pipeline:

db.collection.aggregate([
    { $group: {
        _id: { $toUpper: '$field2' },
        count: { $sum: 1 }
    } },
    { $group: {
        _id: null,
        counts: { 
            $push: { k: '$_id', v: '$count' }
        }
    } },
    { $replaceWith: { $arrayToObject: '$counts' } }    
])

Mongo Playground

Mongodb group by values and count the number of occurence

3 Answers3