Assuming there is a collection where each document contains an array of objects called products.

//Document 1

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 1,
      total: 50,
    },
  ],
}

//Document 2

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 2,
      total: 100,
    },
    {
      productId: "65fasd454daer57f2ads4c",
      quantity: 2,
      total: 100,
    },
  ],
}

//Document 3

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 5,
      total: 200,
    },
  ],
}

Now each document contains an array of products sold, for example product with the id "62aac8cfb5722d4c628a4a24" appears in multiple orders. what I want to do is use aggregate to return an array of objects called sales. each object in the array has the product Id (unique), sum of quantity from all documents and sum of total from all documents.

[{
    productId:"62aac8cfb5722d4c628a4a24"
    quantity:8,
    total:350
},
{
    productId:"65fasd454daer57f2ads4c"
    quantity:2,
    total:200
}]

CodePudding user response：

1. $unwind - Deconstruct products array to multiple documents.
2. $group - Group by products.produtId and sum for products.quantity and products.total.
3. $project - Decorate output documents.

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $group: {
      _id: "$products.productId",
      quantity: {
        $sum: "$products.quantity"
      },
      total: {
        $sum: "$products.total"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      productId: "$_id",
      quantity: 1,
      total: 1
    }
  }
])

Sample Mongo Playground