I'm trying to get a sum of different currency amounts among multiple documents, but I'm struggling with the aggregation.

Initial documents (subdocuments are embedded, so no unwind is needed):

const products = [
  {
    title: 'product1',
    price: [
      {
        amount: 100,
        currency: 'USD',
      },
      {
        amount: 20,
        currency: 'EUR',
      }
    ]
  },
  {
    title: 'product2',
    price: [
      {
        amount: 330,
        currency: 'USD',
      },
    ]
  },
  {
    title: 'product3',
    price: [
      {
        amount: 50,
        currency: 'EUR',
      },
    ]
  },
];

Expected result:

const output = {
  grossIncome: [
    {
      amount: 430,
      currency: 'USD',
    },
    {
      amount: 70,
      currency: 'EUR',
    },
  ]
};

Thank you in advance!

CodePudding user response：

You need this aggregation query:

• First $unwind to deconstruct the array and can get each element.
• Then $group by currency and $sum the amount.

db.collection.aggregate([
  {
    "$unwind": "$price"
  },
  {
    "$group": {
      "_id": "$price.currency",
      "amount": {
        "$sum": "$price.amount"
      }
    }
  }
])

Example here