MongoDB Aggregation for reshaping or grouping value to field names and month

Question

My data is in the format:

 [{ _id: 1, Prom: "I", date: ISOdate(2016-01-01 ... ) },
  { _id: 2, Prom: "P", date: ISOdate(2016-01-01 ... ) },
  { _id: 3, Prom: "D", date: ISOdate(2016-02-01 ... ) },
  { _id: 4, Prom: "I", date: ISOdate(2016-03-01 ... ) },
  { _id: 5, Prom: "I", date: ISOdate(2016-04-01 ... ) },
  { _id: 6, Prom: "D", date: ISOdate(2016-04-01 ... ) },
  { _id: 7, Prom: "P", date: ISOdate(2016-04-01 ... ) },
  ...
  { _id: 512, Prom: "I", date: ISOdate(2016-04-01 ... ) },
  { _id: 632, Prom: "P", date: ISOdate(2016-06-01 ... ) },
  { _id: 656, Prom: "I", date: ISOdate(2016-06-01 ... ) }]

I then aggregate the data to count the number of "P", "I" or "D" per month like so:

db.Collection.aggregate([
{
  $group: {
      _id: { Mnt:"$Mnt", Prom: "$Prom"} ,
      Count: {$sum: 1 },
  }
 }
]);

Which gives me the result as:

 [{ "_id" : { "Mnt" : { "$date" : "2016-01-01T00:00:00.000+0000" }, "Prom" : "D" }, "Count" : 32 }
 { "_id" : { "Mnt" : { "$date" : "2016-01-01T00:00:00.000+0000" }, "Prom" : "P" }, "Count" : 138 }
 { "_id" : { "Mnt" : { "$date" : "2016-01-01T00:00:00.000+0000" }, "Prom" : "I" }, "Count" : 178 }
 { "_id" : { "Mnt" : { "$date" : "2016-02-01T00:00:00.000+0000" }, "Prom" : "D" }, "Count" : 46 }
 { "_id" : { "Mnt" : { "$date" : "2016-02-01T00:00:00.000+0000" }, "Prom" : "P" }, "Count" : 287 }
 { "_id" : { "Mnt" : { "$date" : "2016-02-01T00:00:00.000+0000" }, "Prom" : "I" }, "Count" : 197 }
  ....
 { "_id" : { "Mnt" : { "$date" : "2016-06-01T00:00:00.000+0000" }, "Prom" : "D" }, "Count" : 55 }
 { "_id" : { "Mnt" : { "$date" : "2016-06-01T00:00:00.000+0000" }, "Prom" : "P" }, "Count" : 42 }
 { "_id" : { "Mnt" : { "$date" : "2016-06-01T00:00:00.000+0000" }, "Prom" : "I" }, "Count" : 14 }]

How can I group or project this data to represent my data in the format?

[{ Mnt: ISOdate(2016-01-01 ...), "P": 138, "I": 178, "D": 32 },
 { Mnt: ISOdate(2016-02-01 ...), "P": 287, "I": 197, "D": 46 },
 ...
 { Mnt: ISOdate(2016-06-01 ...), "P": 42, "I": 14, "D": 55 }]

I'm really not finding a way to use the value of 'Prom' as the key in the next aggregation in the pipeline. Also grouping this result by month is problematic. Essentially what we want to do is create a Net Promoter Score from raw data by month, with P - promoters, D - detractors, I - indifferent.

Answer 1

Try this,

 db.Collection.aggregate([
    {
      $group: {
          _id: { Mnt:"$Mnt", Prom: "$Prom"} ,
          Count: {$sum: 1 },
      },
    $group: {
          _id:"$Mnt" ,res: { $push: "$Prom" }      
      },
    $project : { Mnt:$_id , res[0],res[1],res[2]

    }
     }
    ]);