mongoose get property from nested schema after `group`

Question

Having this mongoose schema structure:

OrderSchema = new Schema({
    name: String,
    subtotal: Number,
    tax: Number,
    date: { type: Date, default: Date.now },
    location: { type: ObjectId, ref: 'Location' }
});
...
LocationSchema = new Schema({
    name: String
});
...

I'm trying to get a group aggreation by location, but I need the location name in the result. Here's the aggregate command:

Order.aggregate([
    {
        $group: {
            _id: "$location",
            totalSales: {
                $sum: "$subtotal"
            }
        }
    }
]).exec(function(err, docs) {
   // ...
});

So far, I have this:

[  
   {  
      "_id":"5572fdc84c98893f0fa3472e",
      "result":127.51
   },
   {  
      "_id":"5572f4194c98893f0fa3472c",
      "result":146.24
   },
   {  
      "_id":"5572fdb54c98893f0fa3472d",
      "result":183.36
   }
]

I'm trying to get the location name in there, like this:

[  
   {  
      "_id":"5572fdc84c98893f0fa3472e",
      "name": "Location1",
      "result":127.51
   },
   {  
      "_id":"5572f4194c98893f0fa3472c",
      "name": "Location2",
      "result":146.24
   },
   {  
      "_id":"5572fdb54c98893f0fa3472d",
      "name": "Location3",
      "result":183.36
   }
]

I'm using Express.

What do I need to change in order to accomplish this?

Thank you

Answer 1

While I'm sure there are other ways to do this, I've accomplish this in the past by populating the docs resulting from the aggregate method.

Here's an example:

Order.aggregate([
  {
    $group: {
      _id: "$location",
      location: { $first: "$location" },
      totalSales: {
        $sum: "$subtotal"
      }
    }
  }
]).exec(function(err, docs) {
  Order.populate(docs, { path: 'cat', select: '-_id, name' }, function(err, results) {
    // here you have the populated results
    // I removed the `_id` when populating so it doesn't appear twice
  });    
});

The shape of the results is not identical to what you described in your question, but it will at least contain the name of the location.

[  
   {  
      "_id": "5572fdc84c98893f0fa3472e",
      "location": { "name": "Location1" },
      "result": 127.51
   },
   {  
      "_id": "5572f4194c98893f0fa3472c",
      "location": { "name": "Location2" },
      "result": 146.24
   },
   {  
      "_id": "5572fdb54c98893f0fa3472d",
      "location": { "name": "Location3" },
      "result": 183.36
   }
]