'Mongodb group by values and count the number of occurence

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,
}
mongodbmongodb-queryaggregation-framework


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 J.F.
Solution 2 NeNaD
Solution 3

Related Questions