CODEWITHSUNDEEP
mongodbaggregation-framework
mark
mark

Reputation: 62886

Using Mongo aggregation to calculate sum of values

I have invoices, where each invoice contains a list of items. Each item has (among other things) the following fields:

And each invoice has (among other things) the fields:

The invoices live in a dedicated Mongo collection, called invoices.

I would like to get all the invoices containing the specified item, where for each invoice the following information needs to be returned:

Let us call the tuple <id, date, qty, total> as the invoice projection. Thus the result should be a list of invoice projections.

If an invoice lists the given item twice, then the respective invoice yields two projection instances. If an invoice does not list the given item at all, then this invoice is not present in the result.

Anyway, I am retrieving the required projections using the following Mongo aggregation pipeline:

  pipeline = [
    {$match: {'items.name': req.params.name}},
    {$project: {created: 1, 'items.name': 1, 'items.qty': 1, 'items.total': 1}},
    {$unwind: '$items'},
    {$match: {'items.name': req.params.name}},
    {$project: {created: 1, qty: '$items.qty', total: '$items.total'}}
  ],

The pipeline works like this:

  1. First match all the invoices having an item with the given name. There is a Mongo index on items.name, so this $match is efficient.
  2. An invoice is a large object, so strip all the fields leaving just the following structure: {_id: ?, created: ?, items: [{name: ?, qty: ?, total: ?}, ..., {name: ?, qty: ?, total: ?}]}
  3. Unwind the items array, now we have a list of {_id: ?, created: ?, 'items.name': ?, 'items.qty': ?, 'items.total': ?} objects.
  4. Remove all the items which do not match the given name.
  5. Shape the final invoice projection.

The final list of invoice projections is then put through the following code:

function prepareResult(projections) {
  var res = projections.reduce(function (acc, item) {
    acc.itemCount += item.qty;
    acc.total += item.total;
    delete item.total;
    return acc;
  }, {itemCount: 0, total: 0});
  res.items = projections;
  return res;
}

It sums the qty and total fields over all of the invoice projections and returns a new object containg the projections as well as the calculated sums. The total field is deleted from each and every invoice projection, since it is only the final sum that is needed.

My question - can I move the logic of the prepareResult function into the Mongo aggregation pipeline?

Upvotes: 1

Views: 2998

Answers (1)

Asya Kamsky
Asya Kamsky

Reputation: 42352

You need to add a $group step to the pipeline.

The _id of group will be what you are summing by (in this case a constant since you want a grand total). Since you want to retain the list of invoices, you can accumulate them into an array field via the $push operator. Sums for total and quantity would be handled with $sum.

{$group : { _id : 1,
            Total : { $sum : "$total" },
            ItemCount : { $sum : "$qty" },
            Invoices : { $push : { id : "$_id", created : "$created" }}
} }

Upvotes: 2

Related Questions