CODEWITHSUNDEEP
mongodbaggregation-framework
Sushim Mukul Dutta
Sushim Mukul Dutta

Reputation: 777

Mongodb 3.2 (Aggregation) : Group by multiple values of the same attribute

I have the following count queries : 

db.collection.count({code : {$in : ['delta', 'beta']}});
db.collection.count({code : 'alpha'});
for the given data set 
[
    {
        "code": "detla",
        "name": "delta1"
    },
    {
        "code": "detla",
        "name": "delta2"
    },
    {
        "code": "detla",
        "name": "delta3"
    },
    {
        "code": "detla",
        "name": "delta4"
    },
    {
        "code": "beta",
        "name": "beta1"
    },
    {
        "code": "beta",
        "name": "beta2"
    },
    {
        "code": "beta",
        "name": "beta3"
    },
    {
        "code": "beta",
        "name": "beta4"
    },
    {
        "code": "beta",
        "name": "beta5"
    },
    {
        "code": "alpha",
        "name": "alpha1"
    },
    {
        "code": "alpha",
        "name": "alpha2"
    },
    {
        "code": "alpha",
        "name": "alpha3"
    }
]

Is there any way to achieve this using single aggregation() and nested $group in [email protected]? I understand that this is a classic usecase for mapReduce(), but I am out of option here as the $group by parameters (code in the above example), is a dynamically generated attribute, hence the aggregation stages building is also dynamic.

Expected output is same as the result of the two count queries. -> First count query (delta, beta combined count) -> 9 -> Second count query (alpha count) -> 3

Upvotes: 1

Views: 214

Answers (1)

Ashh
Ashh

Reputation: 46451

You can use below $group aggregation

db.collection.aggregate([
  { "$group": {
    "_id": null,
    "first": {
      "$sum": {
        "$cond": [{ "$in": ["$code", ["detla", "beta"]] }, 1, 0]
      }
    },
    "second": {
      "$sum": {
        "$cond": [{ "$eq": ["alpha", "$code"] }, 1, 0]
      }
    }
  }}
])

But I will prefer to go with the two count queries for the better performance

$in is also released in version 3.4. You can either use $setIsSubset for the prior versions

db.collection.aggregate([
  { "$group": {
    "_id": null,
    "first": {
      "$sum": {
        "$cond": [{ "$setIsSubset": [["$code"], ["detla", "beta"]] }, 1, 0]
      }
    },
    "second": {
      "$sum": {
        "$cond": [{ "$eq": ["alpha", "$code"] }, 1, 0]
      }
    }
  }}
])

Upvotes: 1

Related Questions