Dataweave filtering array based on nested values

Question

I'm trying to filter an array based on some values nested in objects.

My data pertains to customers with outstanding balances on their accounts. I have the age of the balance nested in buckets from 10-29 and 31-.

I want to keep any customers with an account balance over $1000. I also want to keep customers with an account balance of over $200 aged over 30 days. These accounts will get flagged.

From the example, customer 50001 should get removed from the payload, with 50002 and 50003 remaining:

{
    "data": {
        "araging": [
              {  
                "customerid": "50001",
                "aging": [
                    {
                        "agingperiod": "10-29",
                        "totalamount": "59.2"
                    },
                    {
                        "agingperiod": "31-",
                        "totalamount": "50.00"
                    }
                ]
              },
              {  
                "customerid": "50002",
                "aging": [
                    {
                        "agingperiod": "10-29",
                        "totalamount": "0.00"
                    },
                    {
                        "agingperiod": "31-",
                        "totalamount": "246.00"
                    }
                ]
              },
              {  
                "customerid": "50003",
                "aging": [
                    {
                        "agingperiod": "10-29",
                        "totalamount": "1084.60"
                    },
                    {
                        "agingperiod": "31-",
                        "totalamount": "0.00"
                    }
                ]
              }
           ] 
        }
    }
}

My first attempt is to get the customers over $200 for over 30 days.

%dw 2.0
output application/json
---
{
    arAccounts: payload.data.*araging filter ($.aging.totalamount as Number > 200 and $.aging.agingperiod == "31-") map (araging, index) ->
    {
        customerid: araging.customerid,
        totalamount: araging.aging.totalamount as Number
    }
}

That code keeps giving messages about Arrays not being comparable to single values, and I'm unclear on how to iterate through each value of "totalamount." Changing type from String to Number isn't working, since it's an array value. And I need to know how I could sum the values, since any account over $1000 will be flagged. A bonus would be to add the flag field within this same transform.

Thanks in advance.

olamiral · Accepted Answer · 2021-11-02 15:20:53Z

The problem is that the expression $.aging.totalamount returns an array, and not a Number or String. In order to check if the total amount and/or aging period match the criteria, one option is to check if each of these arrays is empty or not after filtering it, as it's done in the following DataWeave expression:

%dw 2.0
output application/json
---
data: {
    araging: (payload.data.araging map 
            ($ ++ {
                flagged: !isEmpty($.aging.totalamount filter $ > 1000)
                    or (
                        !isEmpty($.aging.agingperiod filter $ == "31-")
                        and !isEmpty($.aging.totalamount filter $ > 200)
                    )
            }) 
        ) filter $.flagged
}

Using the provided input payload, the resulting payload is:

{
  "data": {
    "araging": [
      {
        "customerid": "50002",
        "aging": [
          {
            "agingperiod": "10-29",
            "totalamount": "0.00"
          },
          {
            "agingperiod": "31-",
            "totalamount": "246.00"
          }
        ],
        "flagged": true
      },
      {
        "customerid": "50003",
        "aging": [
          {
            "agingperiod": "10-29",
            "totalamount": "1084.60"
          },
          {
            "agingperiod": "31-",
            "totalamount": "0.00"
          }
        ],
        "flagged": true
      }
    ]
  }
}

If you want to return all accounts and use the flagged property to know if the account is matching the criteria, you can remove the filter $.flagged statement from the above DataWeave expression, which will result in:

{
  "data": {
    "araging": [
      {
        "customerid": "50001",
        "aging": [
          {
            "agingperiod": "10-29",
            "totalamount": "59.2"
          },
          {
            "agingperiod": "31-",
            "totalamount": "50.00"
          }
        ],
        "flagged": false
      },
      {
        "customerid": "50002",
        "aging": [
          {
            "agingperiod": "10-29",
            "totalamount": "0.00"
          },
          {
            "agingperiod": "31-",
            "totalamount": "246.00"
          }
        ],
        "flagged": true
      },
      {
        "customerid": "50003",
        "aging": [
          {
            "agingperiod": "10-29",
            "totalamount": "1084.60"
          },
          {
            "agingperiod": "31-",
            "totalamount": "0.00"
          }
        ],
        "flagged": true
      }
    ]
  }
}

Collectives™ on Stack Overflow

Dataweave filtering array based on nested values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related