I have a query result from a mongoose aggregation query that I need to further process, or at best do it in the aggregation itself.
The aggregation looks like this
result = await TokenBalance.aggregate([
{
$match: {
$and: [
{ ethervalue: { $gte: minBalance } },
{
ethervalue: { $lte: maxBalance }
}
]
}
},
{ $limit:limit }
])
This returns an array of Objects of this format
{
"_id": "61013d6dda7d7c0015af5ccf",
"balances": [
{
"address": "0x1fc3ddeb035310930a444c0fa59c01618d5902af",
"symbol": "HBTC",
"balance": 5.21419339e-10,
"usdvalue": 0.000020969961637162402
},
{
"address": "0x1fc3ddeb035310930a444c0fa59c01618d5902af",
"symbol": "NSBT",
"balance": 1.258566,
"usdvalue": 27.427343477595258
},
{
"address": "0x1fc3ddeb035310930a444c0fa59c01618d5902af",
"symbol": "CRV",
"balance": 517.985955847106,
"usdvalue": 806.7017064052314
},
{
"address": "0x1fc3ddeb035310930a444c0fa59c01618d5902af",
"symbol": "USDT",
"balance": 0.003469,
"usdvalue": 0.003470159747979122
}
],
"address": "0x1fc3ddeb035310930a444c0fa59c01618d5902af",
"ethervalue": 0.7604598621232733,
"createdAt": "2021-07-28T11:20:13.927Z",
"updatedAt": "2021-07-28T11:20:13.927Z",
"__v": 0
},
What I need, is the "balances" property to be processed as grouped by symbol and for each of these symbols sum the balance and usdvalue fields.
I would prefer this do be done in the aggregation if possible, but I can not seem to get it right, even not in pure nodejs.
I want the result to be like this:
[
{
symbol: USDC, balance: xxx, usdvalue: yyy
},
{
symbol: USDT, balance: zzz, usdvalue: jjj
}
]
You can use the below approach,
$unwind to deconstruct the balances array$group by symbol and sum balance and usdvalue$addFields to rename _id field to symbol and and remove _id fieldresult = await TokenBalance.aggregate([
{
$match: {
$and: [
{ ethervalue: { $gte: minBalance } },
{ ethervalue: { $lte: maxBalance } }
]
}
},
{ $unwind: "$balances" },
{
$group: {
_id: "$balances.symbol",
balance: { $sum: "$balances.balance" },
usdvalue: { $sum: "$balances.usdvalue" }
}
},
{
$addFields: {
symbol: "$_id",
_id: "$$REMOVE"
}
},
{ $limit:limit }
])