MongoDB aggregate count based on multiple query fields - (Multiple field count)

Question

My collection will look this,

{
    "_id" : ObjectId("55c8bd1d85b83e06dc54c0eb"),
    "name" : "xxx",
    "salary" : 10000,
    "type" : "type1"
}
{
    "_id" : ObjectId("55c8bd1d85b83e06dc54c0eb"),
    "name" : "aaa",
    "salary" : 10000,
    "type" : "type2"
}
{
    "_id" : ObjectId("55c8bd1d85b83e06dc54c0eb"),
    "name" : "ccc",
    "salary" : 10000,
    "type" : "type2"
}

My query params will be coming as,

{salary=10000, type=type2}

so based on the query I need to fetch the count of above query params

The result should be something like this,

{ category: 'type1', count: 500 } { category: 'type2', count: 200 } { category: 'name', count: 100 }

Now I am getting count by hitting three different queries and constructing the result (or) server side iteration I can get the result.

Can anyone suggest or provide me good way to get above result

Answer 1

Your quesstion is not very clearly presented, but what it seems you wanted to do here was count the occurances of the data in the fields, optionally filtering those fields by the values that matches the criteria.

Here the $cond operator allows you to tranform a logical condition into a value:

db.collection.aggregate([
    { "$group": {
        "_id": null,
        "name": { "$sum": 1 },
        "salary": { 
            "$sum": { 
                "$cond": [
                    { "$gte": [ "$salary", 1000 ] },
                    1,
                    0
                ]
            }
        },
        "type": { 
            "$sum": { 
                "$cond": [
                    { "$eq": [ "$type", "type2" ] },
                    1,
                    0
                ]
            }
        }
    }}
])

All values are in the same document, and it does not really make any sense to split them up here as this is additional work in the pipeline.

{ "_id" : null, "name" : 3, "salary" : 3, "type" : 2 }

Otherwise in the long form, which is not very performant due to needing to make a copy of each document for every key looks like this:

db.collection.aggregate([
  { "$project": {
    "name": 1,
    "salary": 1,
    "type": 1,
    "category": { "$literal": ["name","salary","type"] }
  }},
  { "$unwind": "$category" },
  { "$group": {
    "_id": "$category",
    "count": {
      "$sum": {
        "$cond": [
          { "$and": [ 
            { "$eq": [ "$category", "name"] },
            { "$ifNull": [ "$name", false ] }
          ]},
          1,
          { "$cond": [
            { "$and": [
              { "$eq": [ "$category", "salary" ] },
              { "$gte": [ "$salary", 1000 ] }
            ]},
            1,
            { "$cond": [
              { "$and": [
                { "$eq": [ "$category", "type" ] },
                { "$eq": [ "$type", "type2" ] }
              ]},
              1,
              0
            ]}
          ]}
        ]
      }
    }
  }}
])

And it's output:

{ "_id" : "type",   "count" : 2 }
{ "_id" : "salary", "count" : 3 }
{ "_id" : "name",   "count" : 3 }

If your documents do not have uniform key names or otherwise cannot specify each key in your pipeline condition, then apply with mapReduce instead:

db.collection.mapReduce(
    function() {
        var doc = this;
        delete doc._id;

        Object.keys(this).forEach(function(key) {
          var value = (( key == "salary") && ( doc[key] < 1000 ))
              ? 0
              : (( key == "type" ) && ( doc[key] != "type2" ))
              ? 0
              : 1;

          emit(key,value);
        });
    },
    function(key,values) {
        return Array.sum(values);
    },
    {
        "out": { "inline": 1 }
    }
);

And it's output:

    "results" : [
            {
                    "_id" : "name",
                    "value" : 3
            },
            {
                    "_id" : "salary",
                    "value" : 3
            },
            {
                    "_id" : "type",
                    "value" : 2
            }
    ]

Which is basically the same thing with a conditional count, except that you only specify the "reverse" of the conditions you want and only for the fields you want to filter conditions on. And of course this output format is simple to emit as separate documents.

The same approach applies where to test the condition is met on the fields you want conditions for and return 1 where the condition is met or 0 where it is not for the summing the count.

Answer 2

You can use aggregation as following query:

db.collection.aggregate({
        $match: {
            salary: 10000,
            //add any other condition here
        }
    }, {
        $group: {
            _id: "$type",
            "count": {
                $sum: 1
            }
        }
    }, {
        $project: {
            "category": "$_id",
            "count": 1,
            _id: 0
        }
    }