Group Multiple Values in Aggregation

Question

I want to group the all field of a collection with unique total. Let's assume there is collection like this:

 id  country state operator
 121  IN       HR    AIRTEL
 212  IN       MH    AIRTEL
 213  US       LA    AT&T
 214  UK       JK    VODAFONE

Output should be like this:

{
    "country": { "IN": 2, "US":1, "UK":1 }, 
    "state": { "HR":1, "MH":1, "LA":1, "JK": 1 }, 
    "operator": { "AIRTEL":2, "AT&T": 1, "VODAFONE": 1 }
}

I am trying to use mongo aggregation framework, but can't really think how to do this?

It is not possible using the aggregation framework. You need to use Map-Reduce. — BatScream
– BatScream, Commented Apr 30, 2015 at 22:53
docs.mongodb.org/manual/tutorial/map-reduce-examples - this should be a very simple and effective starting point. — BatScream
– BatScream, Commented Apr 30, 2015 at 23:44

Neo-coder · Accepted Answer · 2015-05-06 08:26:08Z

I find out some similar to your output using aggregation check below code

db.collectionName.aggregate({
  "$group": {
    "_id": null,
    "countryOfIN": {
      "$sum": {
        "$cond": [{
          $eq: ["$country", "IN"]
        }, 1, 0]
      }
    },
    "countryOfUK": {
      "$sum": {
        "$cond": [{
          $eq: ["$country", "UK"]
        }, 1, 0]
      }
    },
    "countryOfUS": {
      "$sum": {
        "$cond": [{
          $eq: ["$country", "US"]
        }, 1, 0]
      }
    },
    "stateOfHR": {
      "$sum": {
        "$cond": [{
          $eq: ["$state", "HR"]
        }, 1, 0]
      }
    },
    "stateOfMH": {
      "$sum": {
        "$cond": [{
          $eq: ["$state", "MH"]
        }, 1, 0]
      }
    },
    "stateOfLA": {
      "$sum": {
        "$cond": [{
          $eq: ["$state", "LA"]
        }, 1, 0]
      }
    },
    "stateOfJK": {
      "$sum": {
        "$cond": [{
          $eq: ["$state", "JK"]
        }, 1, 0]
      }
    },
    "operatorOfAIRTEL": {
      "$sum": {
        "$cond": [{
          $eq: ["$operator", "AIRTEL"]
        }, 1, 0]
      }
    },
    "operatorOfAT&T": {
      "$sum": {
        "$cond": [{
          $eq: ["$operator", "AT&T"]
        }, 1, 0]
      }
    },
    "operatorOfVODAFONE": {
      "$sum": {
        "$cond": [{
          $eq: ["$operator", "VODAFONE"]
        }, 1, 0]
      }
    }
  }
}, {
  "$group": {
    "_id": null,
    "country": {
      "$push": {
        "IN": "$countryOfIN",
        "UK": "$countryOfUK",
        "US": "$countryOfUS"
      }
    },
    "STATE": {
      "$push": {
        "HR": "$stateOfHR",
        "MH": "$stateOfMH",
        "LA": "$stateOfLA",
        "JK": "$stateOfJK"
      }
    },
    "operator": {
      "$push": {
        "AIRTEL": "$operatorOfAIRTEL",
        "AT&T": "$operatorOfAT&T",
        "VODAFONE": "$operatorOfVODAFONE"
      }
    }
  }
}, {
  "$project": {
    "_id": 0,
    "country": 1,
    "STATE": 1,
    "operator": 1
  }
})

using $cond created groups of matched data and pushed them in second groups to combine.

Blakes Seven · Accepted Answer · 2015-07-05 07:51:15Z

An output format like you are looking for is not really suited to the aggregation framework since you are tranforming part of your data in to "key" names. The aggregation framework does not do this but rather sticks to database "best practice" as does not transform "data" to "key" names in any way.

You can perform a mapReduce operation instead with allows more flexibilty with the manipulation, but not as good performance due to the need to use JavaScript code to perform the manipulation:

db.collection.mapReduce(
  function () {
    var obj = {},
        doc = this;

    delete doc._id;
    Object.keys(doc).forEach(function(key) {
      obj[key] = {};
      obj[key][doc[key]] = 1;
    });
    emit( null, obj );
  },
  function (key,values) {
    var result = {};

    values.forEach(function(value) {
      Object.keys(value).forEach(function(outerKey) {
        Object.keys(value[outerKey]).forEach(function(innerKey) {
          if ( !result.hasOwnProperty(outerKey) ) {
            result[outerKey] = {};
          }
          if ( result[outerKey].hasOwnProperty(innerKey) ) {
            result[outerKey][innerKey] += value[outerKey][innerKey];
          } else {
            result[outerKey][innerKey] = value[outerKey][innerKey];
          }
        });
      });
    });

    return result;
  },
  { "out": { "inline": 1 } }
)

And in the stucture that applies to all mapReduce results:

{
    "results" : [
            {
                    "_id" : null,
                    "value" : {
                            "country" : {
                                    "IN" : 2,
                                    "US" : 1,
                                    "UK" : 1
                            },
                            "state" : {
                                    "HR" : 1,
                                    "MH" : 1,
                                    "LA" : 1,
                                    "JK" : 1
                            },
                            "operator" : {
                                    "AIRTEL" : 2,
                                    "AT&T" : 1,
                                    "VODAFONE" : 1
                            }
                    }
            }
    ]
}

For the aggregation framework itself, it is better suited to producing aggregation results that are more consistently structured:

db.mapex.aggregate([
    { "$project": {
        "country": 1,
        "state": 1,
        "operator": 1,
        "type": { "$literal": ["country","state","operator"] }
    }},
    { "$unwind": "$type" },
    { "$group": {
        "_id": {
           "type": "$type",
           "key": { "$cond": {
               "if": { "$eq": [ "$type", "country" ] },
               "then": "$country",
               "else": { "$cond": {
                   "if": { "$eq": [ "$type", "state" ] },
                   "then": "$state",
                   "else": "$operator"
               }}
           }}
        },
        "count": { "$sum": 1 }
    }}
])

Which would output:

{ "_id" : { "type" : "state", "key" : "JK" }, "count" : 1 }
{ "_id" : { "type" : "country", "key" : "UK" }, "count" : 1 }
{ "_id" : { "type" : "country", "key" : "US" }, "count" : 1 }
{ "_id" : { "type" : "operator", "key" : "AT&T" }, "count" : 1 }
{ "_id" : { "type" : "state", "key" : "LA" }, "count" : 1 }
{ "_id" : { "type" : "operator", "key" : "AIRTEL" }, "count" : 2 }
{ "_id" : { "type" : "state", "key" : "MH" }, "count" : 1 }
{ "_id" : { "type" : "state", "key" : "HR" }, "count" : 1 }
{ "_id" : { "type" : "operator", "key" : "VODAFONE" }, "count" : 1 }
{ "_id" : { "type" : "country", "key" : "IN" }, "count" : 2 }

But is fairly easy to transform in client code while iterating the results:

var result = {};

db.mapex.aggregate([
    { "$project": {
        "country": 1,
        "state": 1,
        "operator": 1,
        "type": { "$literal": ["country","state","operator"] }
    }},
    { "$unwind": "$type" },
    { "$group": {
        "_id": {
           "type": "$type",
           "key": { "$cond": {
               "if": { "$eq": [ "$type", "country" ] },
               "then": "$country",
               "else": { "$cond": {
                   "if": { "$eq": [ "$type", "state" ] },
                   "then": "$state",
                   "else": "$operator"
               }}
           }}
        },
        "count": { "$sum": 1 }
    }}
]).forEach(function(doc) {
    if ( !result.hasOwnProperty(doc._id.type) )
        result[doc._id.type] = {};
    result[doc._id.type][doc._id.key] = doc.count;
})

Which gives the final structure in "result":

{
    "state" : {
            "JK" : 1,
            "LA" : 1,
            "MH" : 1,
            "HR" : 1
    },
    "country" : {
            "UK" : 1,
            "US" : 1,
            "IN" : 2
    },
    "operator" : {
            "AT&T" : 1,
            "AIRTEL" : 2,
            "VODAFONE" : 1
    }
}

Collectives™ on Stack Overflow

Group Multiple Values in Aggregation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related