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?

CodePudding user response：

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

CodePudding user response：

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