mongoose get property from nested schema after `group`

154 views Asked by At

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

1

There are 1 answers

0
JME On BEST ANSWER

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
   }
]