mongodb, $sum on array of objects field [duplicate]

Question

I am new to mongodb and may be I am missing something. But having a lot of samples in internet, still having problems to get a total on one field which is part of array of objects. Here is what I am doing:

db.collection.insertMany([
  {
    id: "6002010011500",
    balance: [
      { type: "PR", amount: "1000" },
      { type: "IN", amount: "300" }
    ]
  },
  {
    id: "5001010001005",
    balance: [
      { type: "PR", amount: "-3000" },
      { type: "IN", amount: "-600" }
    ]
  }
])

trying to get total amount in different ways:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      TotalBalance: {
        $sum: "$balance.amount"
      }
    }
  }
])

getting the balance 0 instead of -2300

{ "_id" : null, "TotalBalance" : 0 }

same things with $unwind:

db.collection.aggregate([
  { $unwind: "$balance" },
  {
    $group: {
      _id: null,
      TotalBalance: { $sum: "$balance.amount" }
    }
  }
])

what I am doing wrong?

Thanks

Legna · Accepted Answer · 2019-06-08 23:39:19Z

7

You are storing amount as string whereas it should be a number if you want to use $sum operator. Try

db.collection.insertMany([
  {
    id: "6002010011500",
    balance: [
      { type: "PR", amount: 1000 },
      { type: "IN", amount: 300 }
    ]
  },
  {
    id: "5001010001005",
    balance:
      [
        { type: "PR", amount: -3000 },
        { type: "IN", amount: -600 }
      ]
  }
])

db.collection.aggregate([
  { $unwind: "$balance" },
  {
    $group: {
      _id: null,
      TotalBalance: { $sum: "$balance.amount" }
    }
  }
])

According to MongoDB docs:

Calculates and returns the sum of numeric values. $sum ignores non-numeric values.

edited Jun 8, 2019 at 23:39

Legna

4686 silver badges24 bronze badges

answered Feb 17, 2018 at 12:35

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mongodb, $sum on array of objects field [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related