filter out the objects from JSON array based on conditions using Mule4 dataweave

Question

I have a Json array which has the transactions. Basically, here I'm trying to filter out the records that has equal payments (Payment received should have date greater than payment returned/reversed).

Sample Input :

 [
  { 
    "CreatedDate": "2023-11-05T23:56:34.000Z",
    "payment": "76.25",    
    "invNum": {
      "Name": "OB-0153834"
    },
    "type": "Payment - Received"
  },
  {    
    "CreatedDate": "2023-11-05T00:05:18.000Z",
    "payment": "-76.25",
    "invNum": {     
      "Name": "OB-0153834"
    },
    "type": "Payment - Refunded"
  },
  {
    "CreatedDate": "2023-11-06T00:47:30.000Z",
    "payment": "-45.0",
    "invNum": {
      "Name": "OB-0153837"
    },
    "type": "Payment - Reversed"
  },
  {
    "CreatedDate": "2023-11-06T00:26:07.000Z",
    "payment": "110.0",
    "invNum": {
      "Name": "OB-0153837"
    },
    "type": "Payment - Received"
  },
  {
    "CreatedDate": "2023-11-06T00:43:46.000Z",
    "payment": "45.0",
    "invNum": {
      "Name": "OB-0153837"
    },
    "type": "Payment - Received"
  },
  {
    "CreatedDate": "2023-11-02T18:59:18.000Z",
    "payment": "50.0",
    "invNum": {
      "Name": "OB-0153817"
    },
    "type": "Payment - Received"
  }
]

Expected :

[
  {
    "CreatedDate": "2023-11-02T18:59:18.000Z",
    "payment": "50.0",
    "invNum": {
      "Name": "OB-0153817"
    },
    "type": "Payment - Received"
  },
      {
      "CreatedDate": "2023-11-06T00:26:07.000Z",
      "payment": "110.0",
      "invNum": {
        "Name": "OB-0153837"
      },
      "type": "Payment - Received"
    }
]

Filter out Conditions :

1. With same Name

2. filter out set of records which has same amount but with '-' sign(i.e equal amounts 76 and -76 should be filtered out) and created on same date.

3. Record with - value should have date after the + value .

Answer 1

There are 3 things you mentioned

1. Name match
2. Positive and Negative Sign match
3. If the point 2 matches then its - CreatedDate should be greater than + CreatedDate

By your example, the below condition fails since Created Date 2023-11-05T00:05:18.000Z is not greater than 2023-11-05T23:56:34.000Z

[
  {
    "CreatedDate": "2023-11-05T00:05:18.000Z",
    "payment": "-76.25",
    "invNum": {
      "Name": "OB-0153834"
    },
    "type": "Payment - Refunded"
  },
  {
    "CreatedDate": "2023-11-05T23:56:34.000Z",
    "payment": "76.25",
    "invNum": {
      "Name": "OB-0153834"
    },
    "type": "Payment - Received"
  }
  ]

DW

I grouped By paymentNumber ++ - ++ Name since we want to eliminate matching payment number of a matching Name

%dw 2.0
output application/json
---
flatten(
    (payload orderBy($.payment as Number) 
        groupBy (($.payment replace "-" with "")++"-"++($.invNum.Name)) pluck $)
            filter(item,index)->
                !(((("-" ++ item.payment[1]) default "") == ((item.payment[0]) default "")) 
                    and (((item.CreatedDate[1]) default "") < ((item.CreatedDate[0]) default ""))
                    and (((item."type"[1]) default "") == "Payment - Received" and ((item."type"[0]) default "") != "Payment - Received")))
 

Edit I applied multiple groups

1. To group name and payment
2. To group type over name and payment

DW

%dw 2.0
output application/json
---
%dw 2.0
output application/json
---
flatten(((
    (payload
        groupBy (($.payment replace "-" with "")++"-"++($.invNum.Name)))pluck $) 
        map ($ orderBy(-$.payment) 
        groupBy (($.payment replace "-" with "")++"-"++($.invNum.Name)++"-"++(
            if(($."type")=="Payment - Reversed" or (($."type")=="Payment - Refunded"))
                    "Payment - Rev"
            else "Payment - Received"
        ))))
        map($ pluck $)
        map(item,index)->(
            if((sizeOf(item[0]) default 0) >= (sizeOf(item[1]) default 0))
                do{
                    var item0= (item[0] filter !(
                ((abs($.payment) default "") == (abs(item[1].payment[$$]) default ""))and
                ((($.CreatedDate) default "") < ((item[1].CreatedDate[$$]) default "")) 
                ))
                ---
                if(sizeOf(item[0])==sizeOf(item0))
                (item[0] default []) ++ (item[1] default [])
                else item0
                }               
            else
               do {
                    var item1=(item[1] filter !(
                ((abs($.payment) default "") == (abs(item[0].payment[$$]) default ""))and
                ((($.CreatedDate) default "") > ((item[0].CreatedDate[$$]) default ""))   
                ))
                ---
                if(sizeOf(item[1])==sizeOf(item1))
                (item[0] default []) ++ (item[1] default [])
                else item1
                }
                
            )
)