how to calculate sum of string in mongodb?

user_12034088 0
Viewed 1290

In mongodb I want to calculate sum of partialAmount field which is of string type and in this field values are stored as "20,00","15,00".

How to calculate sum of all values. Both of the queries I have tried are returning 0.

collection.aggregate([
  {
    $group: {
      _id: null,
      sum: { $sum: "$$partialAmount" }
    }
  }
]);

And:

collection.aggregate([
  {
    $group: {
      _id: null,
      totalAmount: {
        $sum: {
          $toDouble: "$partialAmount"
        }
      }
    }
  }
]);
mongodb
1 Answers

Your first query is obviously not going to work cause you're trying to sum strings, and also you have an extra "$" in "$$partialAmount".

Your second query would work if your partialAmount-s were stored in the format "15.00" and "20.00", see here.

If they are saved as "15,00" and "20,00" in the db, your second query should throw an error, not return 0. (If you are actually getting a zero result, then maybe your "partialAmount" field is misspelled in the db, or the field gets lost in a previous stage of the pipeline)

In this case you need either change the values in your db to the "20.00" format, or if this is not feasible, use $split and $concat to convert to the proper format like this, before converting to double and summing up the values.

user_36900650
Related