Aggregating/summing values using DataWeave

Question

I'm dealing with a fairly tricky dataset in Mule and need to use DataWeave to do some maths across a number of records returned in an array. The array I'm handling looks like this:

[
  {
    "id": "1",
    "type": "AAA",
    "metadata": {
      "balance": "500"
    }
  },
  {
    "id": "2",
    "type": "BBB",
    "metadata": {
      "total": "200"
    }
  },
  {
    "id": "3",
    "type": "AAA",
    "metadata": {
      "balance": "-100"
    }
  }
]

The result I'm trying to achieve looks like this:

{
  "X": 200, //sum (all metadata/balance where type=AAA) - (all metadata/total where type=BBB) ** in this case, (500 + -100)-(200)=400
  "Y": 500, //sum all +ve metadata/balance where type=AAA
  "Z": 300 //sum (all metadata/total where type=BBB) + (all -ve metadata/balance where type=AAA * -1) ** in this case, (200)+(-100 * -1)=300
}

I've crawled through as much documentation as possible and am struggling to find an answer. To make things even more awkward, the values I need to sum are presented in string format in the inbound message and need to be presented in integer format in the outbound message.

Any guidance would be appreciated.

Answer 1

This is one way to do it.

%dw 1.0
%output application/json
---
using (
  aaa = payload[?($.type == "AAA")],
  bbbTotal = sum payload[?($.type == "BBB")].metadata.total
) {
    X: sum aaa.metadata.balance - bbbTotal,
    Y: sum aaa.metadata.balance,
    Z: bbbTotal - sum aaa.metadata.balance[?($ < 0)]
  }   

Answer 2

Try this one:

%dw 1.0
%output application/json
%function getMetadataByType(type) (payload[?($.type == type)].metadata)
%function sumPositiveBalance(type) ((sum getMetadataByType(type).balance[?($ > 0)]) default 0)
%function sumNegativeBalance(type) ((sum getMetadataByType(type).balance[?($ < 0)]) default 0)
%function sumAllBalance(type) ((sum getMetadataByType(type).balance) default 0)
%function sumTotal(type) ((sum getMetadataByType(type).total) default 0)
%var valueOfX = sumAllBalance("AAA") - sumTotal("BBB")
%var valueOfY = sumPositiveBalance("AAA")
%var valueOfZ = sumTotal("BBB") + (sumNegativeBalance("AAA") * -1)
---
{
    X: valueOfX,
    Y: valueOfY,
    Z: valueOfZ
}