I have this collection

{ 
    "branchName" : "Branch1", 
    "products" : [
        {
            "name" : "Chocolate", 
            "quantity" : NumberInt(2), 
            "price" : "64"
        }, 
        {
            "name" : "Torta Galaxy", 
            "quantity" : NumberInt(2), 
            "price" : "30"
        }, 
        {
            "name" : "Torta Chocolate", 
            "quantity" : NumberInt(1), 
            "price" : "91"
        }
}

I want to output like

BranchName, name, quantity, price, subtotal
Branch1, Chocolate, 2, 64, 128
Branch1, Torta Galaxy, 2, 30, 60
Branch1, Torta Chocolate, 1, 91, 91

The problem is to project array elements rows in results as I couldn't do it by project. So that I could make arithmetic operation on these values like sum or multiply

CodePudding user response：

You can use unwind aggregation

db.collectionName.aggregate([
   {$unwind: '$products'}
])

CodePudding user response：

1. $unwind - Deconstruct products array field to multiple documents.

2. $project - Display document in the desired output format.

   For subtotal field, convert product.price to decimal ($toDecimal) and next $multiply with product.quantity.

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $project: {
      "_id": 0,
      "branchName": "$branchName",
      "name": "$products.name",
      "quantity": "$products.quantity",
      "price": "$products.price",
      "subtotal": {
        "$multiply": [
          "$products.quantity",
          {
            "$toDecimal": "$products.price"
          }
        ]
      }
    }
  }
])

Sample Mongo Playground