CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
mhlavacka
mhlavacka

Reputation: 701

Count how many times is the same value present in array

After grouping in aggregation I have elements that look the following way:

{_id: ID, items: ["yes", "no", "yes", "no", "no", "yes", "no", "no", "no"]}

I'd like to project how many times is certain value present in items array

To end up with a document in the format of:

{_id: ID, yes: 3, no: 6}

Upvotes: 2

Views: 1735

Answers (3)

Parshuram Kalvikatte
Parshuram Kalvikatte

Reputation: 1646

db.collection.aggregate([ 
  "$project" : {
    _id : 1,
    "yes" : {
      $size : {
        "$map" : {
          input : "$items",
          as : "Yes",
          in : {"$eq" : ["$$Yes","yes"]}
        }
      }
    }
    "no" : {
      "$size" : {          
        "$map" : {
          input : "$items",
          as : "No",
          in : {"$eq" : ["$$No","no"]}
        }
      }
    }
  }
])

Upvotes: 0

chridam
chridam

Reputation: 103365

Use a combination of $filter and $size operators to filter the items array based on a given condition and return the count respectively. For example, run the following pipeline to get the desired result. Of course this assumes the distinct binary elements from the items list "yes" and "no" are known beforehand:

db.collection.aggregate([
    {
        "$project": {
            "yes": {
                "$size": {
                    "$filter": {
                        "input": "$items",
                        "as": "item",
                        "cond": {
                            "$eq": ["$$item", "yes"]
                        }
                    }
                }
            },
            "no": {
                "$size": {
                    "$filter": {
                        "input": "$items",
                        "as": "item",
                        "cond": {
                            "$eq": ["$$item", "no"]
                        }
                    }
                }
            }
        }
    }
])

For a solution where the distinct elements are not known in advance, then you would need to $unwind the items array, group the flattened documents and aggregate the counts as:

db.collection.aggregate([
    { "$unwind": "$items" },
    {
        "$group": {
            "_id": "$items",
            "id": { "$first": "$_id" },
            "count": { "$sum": 1 }
        }
    },
    {
        "$group": {
            "_id": "$id",
            "counts": {
                "$push": {
                    "item": "$_id",
                    "count": "$count"
                }
            }
        }
    }
])

Sample Output

{
    "_id" : ID,
    "counts" : [ 
        {
            "item" : "yes",
            "count" : 3
        }, 
        {
            "item" : "no",
            "count" : 6
        }
    ]
}

Upvotes: 4

Marcelo Risse
Marcelo Risse

Reputation: 514

You could add two more phase to your aggregation pipeline

{ $unwind: '$items' }

Then group by items again,

{ $group: { _id: '$items', count: { $sum: 1 }}}

This will generates an array like:

[{ _id: 'yes', count: 3}, { _id: 'no', count: 6}]

Upvotes: 0

Related Questions