MongoDB aggregation sum values and group by property-CodePudding

I have a collection that looks like this

{
 balance: string
 symbol: string
 address: string
}

I want to query that collection using an aggregation as following:

input array addresses[]

query collection where collection.address contained in provided address Array and SUM all balances grouped by symbol.

The desired output should look like this:

[
  {symbol: xy, balance: summedBalanceForThisSymbol},
  {symbol: yz, balance: summedBalanceForThisSymbol},
]

I am rather new to mongoDB, this is what I have which doesnt work at all.

  const pipe = [
        {
            match: [
                {
                    address: { $in: addresses } //addresses is an array of string values
                }
            ]
        },
        {
            group: {
                objectId: "$symbol",
                balance: { $sum: "$balance" }
            }
        }
    ]

I have to use objectId instead of _id because the SDK I am using requires to do so. I also need the string balance values to be converted to a number (I guess double) before summing up.

CodePudding user response：

You are very close, you just have some minor syntax errors, this is how you should do it:

(One thing to mentioned is this requires balance to be a valid number string, if not the pipeline will throw an error)

db.collection.aggregate([
  {
    $match: {
      address: {
        $in: addresses
      }
    }
  },
  {
    $group: {
      _id: "$symbol",
      sum: {
        $sum: {
          "$toInt": "$balance"
        }
      }
    }
  }
])

Mongo Playground