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

MongoDB nested map aggregation to sum a array element

I have the following data:

[
  {
    "result": {
      "events": [
        {
          "amount": [
            [
              1623224700000,
              "33333333"
            ],
            [
              1623224760000,
              "33333333"
            ],
            [
              1623224820000,
              "33333334"
            ]
          ],
          
        }
      ],
      
    }
  }
]

In amount, the second element (str) is the amount I need to sum.

My pipeline:

db.collection.aggregate([
  {
    $addFields: {
      "amount_sum": {
        $sum: {
          $map: {
            input: "$result.events",
            as: "events",
            in: {
              $map: {
                input: "$events",
                as: "event",
                in: {
                  $toInt: {
                    $last: "$event.amount"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

Output:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "amount_sum": 0,
    "result": {
      "events": [
        {
          "amount": [
            [
              1.6232247e+12,
              "33333333"
            ],
            [
              1.62322476e+12,
              "33333333"
            ],
            [
              1.62322482e+12,
              "33333334"
            ]
          ]
        }
      ]
    }
  }
]

I’ve searched every (nested) $map, $reduce question/answer, but can’t figure out why this doesn’t produce a result.

Using nested $unwind (see below) gives the desired result, but I need to add a field to the original document.

db.collection.aggregate([
  {
    $unwind: "$result.events"
  },
  {
    $unwind: "$result.events.amount"
  },
  {
    $addFields: {
      amount_sum: {
        $sum: {
          $toInt: {
            $last: "$result.events.amount"
          }
        }
      }
    }
  },
  {
    $group: {
      _id: {
        id: "$_id"
      },
      sum_amount: {
        $sum: "$amount_sum"
      }
    }
  }
])

Output:

[
  {
    "_id": {
      "id": ObjectId("5a934e000102030405000000")
    },
    "sum_amount": 100000000
  }
]

What’s the magic MongoDB command I’m missing here?

Tags:

2

Answers


  1. 0

    Consider refactoring your schema if possible. Currently, the highly nested schema and storing numerics as text is introducing high complexity to query composing. Nevertheless, you can still proceed with layered $sum and $map to achieve what you need.

    db.collection.aggregate([
  {
    $addFields: {
      "amount_sum": {
        $sum: {
          "$map": {
            "input": "$result.events",
            "as": "e",
            "in": {
              $sum: {
                "$map": {
                  "input": "$$e.amount",
                  "as": "a",
                  "in": {
                    $toInt: {
                      "$arrayElemAt": [
                        "$$a",
                        1
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Your current aggregate will not create an array, BUT an array of arrays, which is not compatible with $sum operator.

    You can refactor it like this:

    db.collection.aggregate([
  {
    $set: {
      "amount_sum": {
        $sum: {
          $first: {
            $map: {
              input: "$result.events",
              as: "event",
              in: {
                $map: {
                  input: "$$event.amount",
                  as: "amount",
                  in: {
                    $toInt: {
                      $last: "$$amount"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

    Working example

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

Back To Top
Search