MongoDB - Perform aggregations on bucketed data-CodePudding

I have a collection of nested documents, divided into buckets belonging to a single business id.

To illustrate, the following represents a document related to an invoice from business n. 1022 in which 10 roses, 20 oranges and 15 apples were sold:

sample_doc = {
'business_id': '32044',
'dt_op': Timestamp('2018-10-02 12:16:12'),
'transactions': [
    {'Product name': 'Rose', "Quantity": 10},
    {'Product name': 'Orange', "Quantity": 20},
    {'Product name': 'Apple', "Quantity": 15}
    ]
}

I would like to get the total number of sales (sum of 'Quantity') for each product ('Product name') within a defined 'business_id'.

I tried, using Compass, to:

# Stage 1: $match
{
  business_id: "1022"
}

#Stage 2: $group
{
  _id: "$transactions.Product name",
  TotalSum: {
    $sum: "transactions.Quantity"
  }
}

But a nested list of documents is returned, without performing sums.

How can I correctly perform the aggregation pipeline to get the total number of sales (sum of 'Quantity') for each product ('Product name') within a defined 'business_id'?

CodePudding user response：

You are very close, all you're missing is a single $unwind before the $group stage:

db.collection.aggregate([
  {
    $match: {
      business_id: "1022"
    }
  },
  {
    $unwind: "$transactions"
  },
  {
    $group: {
      _id: "$transactions.Product name",
      TotalSum: {
        $sum: "$transactions.Quantity"
      }
    }
  }
])

Mongo Playground