My data looks like this:

[
   {
      "_id":"61717cafd351f3ae8b6d205a",
      "restaurant":"Hogwarts",
      "purchasedAt":"2021-10-20T17:47:40.166Z",
      "products":[
         {
            "name":"Meat Samosa",
            "price":3.95,
            "quantity":1,
            "_id":"61717cafd351f3ae8b6d205b"
         },
         {
            "name":"Pilau Rice",
            "price":2.95,
            "quantity":1,
            "_id":"61717cafd351f3ae8b6d205f"
         }
      ]
   },
   {
      "_id":"61717cb2d351f3ae8b6dd05b",
      "restaurant":"Hogwarts",
      "purchasedAt":"2021-10-20T03:14:11.111Z",
      "products":[
         {
            "name":"Pilau Rice",
            "price":2.95,
            "quantity":1,
            "_id":"61717cb2d351f3ae8b6dd05d"
         }
      ]
   },
]

I am trying to find a query that will get me all the products (no duplicates) and their quantities added up. Notice that the products id are different even when they are the same(same name) Ideally my response would look like this

[
  { 
    name: "Meat Samosa",
    price: 3.95,
    quantity: 1
 },
 {
   name: "Pilau Rice",
   price: 2.95,
   quantity: 2
 }
] 

CodePudding user response：

• $project to show required fields
• $unwind deconstruct the products array
• $group by name and get the first price and count the quantity sum
• $project to show required fields

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

Playground