CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkgrouping
maephisto
maephisto

Reputation: 5182

Group and count with condition

I'm trying to group a set of documents and count them based on their value:

{ item: "abc1", value: 1 }
{ item: "abc1", value: 1 }
{ item: "abc1", value: 11 }
{ item: "xyz1", value: 2 }

I would like to group by item and get in return a count of how many times the value is bigger than 10 and how many times smaller:

{ item: "abc1", countSmaller: 2, countBigger: 1 }
{ item: "xyz1", countSmaller: 1, countBigger: 0 }

Upvotes: 32

Views: 54946

Answers (5)

Aviko
Aviko

Reputation: 1199

Very similar to Anand's answer, but with one step:

$group: {
    _id: "$item",
    countSmaller: { $sum: {$cond: [ { $lt: ["$value", 10 ] }, 1, 0]} },
    countBigger: { $sum: {$cond: [ { $gt: [ "$value", 10 ] }, 1, 0]} }
}

Upvotes: 1

Aman Kumar Gupta
Aman Kumar Gupta

Reputation: 3011

To count data based on the condition in the $group stage we can make use of "$accumulator" operator which is changed in MongoDb version 5.0

So based on your requirement we can implement using this aggregation stage -

db.products.aggregate([
  {
    $group: {
      _id: "$item",
      totalCounts: { $sum: 1 },
      countsMeta: {
        $accumulator: {
          init: function () {
            // Set the initial state
            return { countSmaller: 0, countBigger: 0 };
          },
          accumulate: function (state, value) {
            // Define how to update the state
            return value < 10
              ? { ...state, countSmaller: state.countSmaller + 1 }
              : { ...state, countBigger: state.countBigger + 1 };
          },
          accumulateArgs: ["$value"], // Pass the desired argument to the accumulate function
          merge: function (state1, state2) {
            /* 
                Executed when the operator performs a merge,
                Merge may happen in two cases : 
                 1). $accumulator is run on a sharded cluster. The operator needs to merge the 
                     results from each shard to obtain the final result.
                 2). A single $accumulator operation exceeds its specified memory limit. 
                     If you specify the allowDiskUse option, the operator stores the 
                     in-progress operation on disk and finishes the operation in memory. 
                     Once the operation finishes, the results from disk and memory are 
                     merged together using the merge function.
                The merge function always merges two states at a time. In the event that more 
                than two states must be merged, the resulting merge of two states is merged 
                with a single state. This process repeats until all states are merged.     
            */

            return {
              countSmaller: state1.countSmaller + state2.countSmaller,
              countBigger: state1.countBigger + state2.countBigger,
            };
          },
          finalize: function (state) {
            // After collecting the results from all documents,
            return state;
          },
          lang: "js",
        },
      },
    },
  },
]);

This execution gives the following result

Result Image Snapshow

For more information about stage and operator refer the following link

https://www.mongodb.com/docs/manual/reference/operator/aggregation/accumulator/

Hope this will help you or somebody else. Thanks!

Happy Coding :-)

Upvotes: 2

user2262292
user2262292

Reputation: 129

If anyone is looking for Java code for this scenario(fields updated as per my need): 

Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.project("environment").and("success").applyCondition(ConditionalOperators.when(Criteria.where("deploymentStatus").is("SUCCESS"))
                        .then(1)
                        .otherwise(0)).and("failed").applyCondition(ConditionalOperators.when(Criteria.where("deploymentStatus").is("FAILURE"))
                        .then(1)
                        .otherwise(0)),
                Aggregation.group("environment").sum("success").as("success").sum("failed").as("failed"));

Upvotes: 3

Sede
Sede

Reputation: 61225

You need to use the $cond operator. Here 0 is value less than 10 and 1 value greater than 10. This doesn't exactly give you expected output. Perhaps someone will post better answer. 

db.collection.aggregate(
    [
        {
            "$project": 
                {
                    "item": 1, 
                    "value": 
                        {
                            "$cond": [ { "$gt": [ "$value", 10 ] }, 1, 0 ] 
                        }
                 }
         }, 
         {
             "$group": 
                 {
                     "_id": { "item": "$item", "value": "$value" },                       
                     "count": { "$sum": 1 }
                 }
         }, 
         {
             "$group": 
                 { 
                     "_id": "$_id.item", 
                     "stat": { "$push": { "value": "$_id.value", "count": "$count" }}
                 }
          }
    ]
)

Output:

{
        "_id" : "abc1",
        "stat" : [
                {
                        "value" : 1,
                        "count" : 2
                },
                {
                        "value" : 0,
                        "count" : 2
                }
        ]
}
{ "_id" : "xyz1", "stat" : [ { "value" : 0, "count" : 1 } ] }

You will need to convert your value to integer or float

Upvotes: 4

Anand Jayabalan
Anand Jayabalan

Reputation: 12904

What you need is the $cond operator of aggregation framework. One way to get what you want would be:

db.foo.aggregate([
    {
        $project: {
            item: 1,
            lessThan10: {  // Set to 1 if value < 10
                $cond: [ { $lt: ["$value", 10 ] }, 1, 0]
            },
            moreThan10: {  // Set to 1 if value > 10
                $cond: [ { $gt: [ "$value", 10 ] }, 1, 0]
            }
        }
    },
    {
        $group: {
            _id: "$item",
            countSmaller: { $sum: "$lessThan10" },
            countBigger: { $sum: "$moreThan10" }
        }
    }
])

Note: I have assumed value to numeric rather than String.

Output:

{
        "result" : [
                {
                        "_id" : "xyz1",
                        "countSmaller" : 1,
                        "countBigger" : 0
                },
                {
                        "_id" : "abc1",
                        "countSmaller" : 2,
                        "countBigger" : 2
                }
        ],
        "ok" : 1
}

Upvotes: 48

Related Questions