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 } 

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 }   

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