Question 310091 in Mongodb Question posted in
The official documentation can be found here.

MongoDB Aggregate how to sum values in object array

I’m trying to add up two amounts within an object inside an Aggregate pipeline.

Here’s my sandbox: https://mongoplayground.net/p/LIvksL-UGur

Document:

[
  {
    "result": {
      "invoices": [
        {
          "product": "baseball",
          "amount": 4,
          "tax": 1
        },
        {
          "product": "basketball",
          "amount": 10,
          "tax": 2
        }
      ]
    }
  }
]

I would like the result to be:

[
  {
    "result": {
      "invoices": [
        {
          "product": "baseball",
          "amount": 4,
          "tax": 1,
          "total": 5
        },
        {
          "product": "basketball",
          "amount": 10,
          "tax": 2,
          "total": 12
        }
      ]
    }
  }
]

Here’s what I thought would work:

db.collection.aggregate([
  {
    $set: {
      "result.invoices": {
        "total": "$result.invoices.amount + $result.invoices.tax"
      }
    }
  }
])

The total is empty because it’s trying to add two arrays, which I understand by trying this:

db.collection.aggregate([
  {
    $set: {
      "result.invoices": {
        "total": "$result.invoices.amount"
      }
    }
  }
])

…which gives this:

[
  {
    "result": {
      "invoices": [
        {
          "product": "baseball",
          "amount": 4,
          "tax": 1,
          "total": [
             4,
             10
           ]
        },
        {
          "product": "basketball",
          "amount": 10,
          "tax": 2,
          "total": [
             4,
             10
           ]
        }
      ]
    }
  }
]

How do I do it the right way?

Note: I realize this is a very simple example, and I can add the calculation after getting the results. This just illustrates a more complex problem I’m trying to solve.

Tags:

3

Answers


  1. 0

    To achieve the desired result where you add amount and tax for each item in the invoices array, you need to use the $map operator within an $addFields or $set stage in your MongoDB aggregation pipeline. This operator allows you to transform each item in an array.

    Here’s the modified aggregation pipeline that should work for your scenario:

    db.collection.aggregate([
  {
    $set: {
      "result.invoices": {
        $map: {
          input: "$result.invoices",
          as: "invoice",
          in: {
            product: "$$invoice.product",
            amount: "$$invoice.amount",
            tax: "$$invoice.tax",
            total: { $add: ["$$invoice.amount", "$$invoice.tax"] }
          }
        }
      }
    }
  }
])

    Explanation:

    • $map: Processes each item in the invoices array.
    • input: "$result.invoices": Specifies the array to process.
    • as: "invoice": Temporary variable representing each item in the array.
    • in: Defines the transformation for each item. Here, we’re keeping the existing fields (product, amount, tax) and adding a new field total, which is the sum of amount and tax for each invoice.

    This pipeline will add a total field to each object in the invoices array, containing the sum of amount and tax.

    Login or Signup to reply.
  2. 0

    As result.invoices is an array field, you need to use $map to iterate through the elements and use $mergeObjects to append the total field there.

    db.collection.aggregate([
  {
    $set: {
      "result.invoices": {
        "$map": {
          "input": "$result.invoices",
          "as": "i",
          "in": {
            "$mergeObjects": [
              "$$i",
              {
                "total": {
                  $sum: [
                    "$$i.amount",
                    "$$i.tax"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  3. 0

    You can use this:

    db.collection.aggregate([
  {
    "$project": {
      _id: 0,
      "result.invoices": {
        "$map": {
          "input": "$result.invoices",
          "as": "invoice",
          "in": {
            "$mergeObjects": [
              "$$invoice",
              {
                "total": {
                  "$sum": [
                    "$$invoice.amount",
                    "$$invoice.tax"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

    See HERE for a working example.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search