CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Oleg2tor
Oleg2tor

Reputation: 521

MongoDB Aggregation Multiple Keys

I trying to aggregate employees using department and status to calculate summ for each status. From this:

{
  name : "Employee_1",
  department: "hr",
  status : "exist"
},
{
  name : "Employee_2",
  department: "acme",
  status : "absent"
},
{
  name : "Employee_3",
  department: "acme",
  status : "absent"
}
...

to this:

{
  department: "hr",
  statuses: {
    exist: 1,
    absent: 0
  }
},
{
  department: "acme",
  statuses: {
    exist: 0,
    absent: 2
  }
}
...

I was try to do it via:

Employee.aggregate(  
        { $group: {
            _id: '$department',
            statuses: { $addToSet: "$status" }
        }},
        { $project: { _id: 1, statuses: 1 }},
        function(err, summary) {
            console.log(summary);
        }
    );

I get only statuses in array, produced by "$addToSet":

{
  department: "hr",
  statuses: [
      'exist',
      'absent'
  ]
},
{
  department: "acme",
  statuses: [
      'exist',
      'absent'
  ]
}
...

How to right put "{$sum: 1}" for each of statuses? Thank for you'r response.

Upvotes: 1

Views: 625

Answers (2)

Miguel Cartagena
Miguel Cartagena

Reputation: 2606

If the possible status are just exist and absent, you could use the $cond operator in conjunction with $eq operator.

{ 
    $group : {
        _id : '$department',
        exist :  { $sum : {  $cond : [ { $eq : ['$status', 'exist'] }  ,1,0 ]  } },
        absent :  { $sum : {  $cond : [ { $eq : ['$status', 'absent'] }  ,1,0 ]  } }
    } 
}

After grouping, you could project as you want

Upvotes: 1

innoSPG
innoSPG

Reputation: 4656

mapReduce can be used to solve the problem.

1) define the following map function

var mapFunction = function() {
  var key = this.department;
  var nb_match_bar2 = 0;
  var status_exist = 0;
  var status_absent = 0;
  if( this.status=="exist" ){
    status_exist = 1;
  }else{
    status_absent= 1;
  }
  var value = {
    department: this.department,
    statuses:{
      exist: status_exist,
      absent: status_absent
    }
  };

  emit( key, value );
};

2) define the reduce function

var reduceFunction = function(key, values) {

  var reducedObject = {
    department: key,
    statuses: {
      exist: 0,
      absent:0
    }
  };
  values.forEach( function(value) {
    reducedObject.statuses.exist += value.statuses.exist;
    reducedObject.statuses.absent += value.statuses.absent;
  }
  );
  return reducedObject;
};

3) run mapduce and store the result in the collection map_reduce_result

db.rien.mapReduce(mapFunction, reduceFunction, {out:'map_reduce_result'})

4) finally, query the collection map_reduce_result

db.map_reduce_result.find()

For your collection, it should give something like this

{
   "_id" : "acme",
   "value" : {
      "department" : "acme",
      "statuses" : {
         "exist" : 0,
         "absent" : 2
      }
   }
}
{
   "_id" : "hr",
   "value" : {
      "department" : "hr",
      "statuses" : {
         "exist" : 1,
         "absent" : 0
      }
   }
}

that you can easily customize.

I hope it helps.

Upvotes: 1

Related Questions