Group and count with condition

Question

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 }

Godliman

Answer 1

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
}  

Custodial

Take into consideration the value field is a string, so you may want to convert that key value to a number. – Polestar

@chridam, thanks for the comment. I have added a note in my answer regarding my assumption of the value field as numeric. I'll leave that part as an exercise to the OP :) – Custodial

My bad, I had not seen the note, hideously stowed between code :P – Polestar

Answer 2

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

Bigg

Answer 3

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"));

Comfortable

Answer 4

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

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 :-)

Elene

Answer 5

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]} }
}

Plasia