How to dynamically convert an list of strings into key value pair using dataweave 2.0

Question

I have a below input where I have key "Actual_Amount" which is a list of strings. I want to convert this key into multiple keys based on the no of values in the string.

Input:

[
  {
    "Billing_ID": "Default",
    "Product": "License Fee",
    "Region": "EMEA",
    "Actual_Amount": "0,49902.95,0"
  },
  {
    "Billing_ID": "Default",
    "Product": "License Fee",
    "Region": "APAC",
    "Actual_Amount": "0,0,0,0,0"
  }
]

Expected Output:

[
  {
    "Billing_ID": "Default",
    "Product": "License Fee",
    "Region": "EMEA",
    "Actual_Amount_1": "0",
    "Actual_Amount_2": "49902.95",
    "Actual_Amount_3": "0"
  },
  {
    "Billing_ID": "Default",
    "Product": "License Fee",
    "Region": "APAC",
    "Actual_Amount_1": "0",
    "Actual_Amount_2": "0",
    "Actual_Amount_3": "0",
    "Actual_Amount_4": "0",
    "Actual_Amount_5": "0"
  }
]

I tried to convert Actual_Amount into array and try but could not get the desired output.

Can someone help me with this?

TIA

Answer 1

You can split comma separated amount using splitBy and iterate over it using map

%dw 2.0
output application/json
---
payload map{
    ($ - "Actual_Amount"),
    ($.Actual_Amount splitBy "," map(item,index)-> {
        ("Actual_Amount_" ++ (index + 1)): item
    })
}

Answer 2

%dw 2.0
output application/json

---
payload map ((item, index) -> {
    Billing_ID: item.Billing_ID,
    Product: item.Product,
    Region: item.Region,
    (item.Actual_Amount splitBy  "," map ((item, index) -> {
        ("Actual_Amount_" ++ index +1):item
    }))
} )