Transform JSON Data

Hello All,

I’m trying to transform the JSON data but not able to achieve it. Please find the below JSON data and the expected output.
Can anyone suggest how can I achieve this?
[
{
“BillAmt”: 32.50,
“DocumentNumber”: 33395496,
“Permalink”: “Test-company”,
“NSID”: “1865041”,
“OpportunityID”: “006C000000g0uNB”,
“BillStatus”: [
“paidInFull”,
“NotpaidInFull”
],
“ApplyingTransAmount”: [
“-82.5”,
“-32.5”
],
“ApplyingTransDocNum”: [
“1200093-2462019-11341”,
“US_3d11e440 UCP_141”
],
“ApplyingTransType”: [
“_vendorPayment”,
“_vendorCredit”
]
},
{
“BillAmt”: 50.00,
“DocumentNumber”: 33396586,
“Permalink”: “Chase”,
“NSID”: “1865043”,
“OpportunityID”: “006C000000g0uNB”,
“BillStatus”: [
“paidInFull”,
“Fail”,
“partiallypaid”
],
“ApplyingTransAmount”: [
“-82.5”,
“-20.0”,
“-62.5”
],
“ApplyingTransDocNum”: [
“1200093-2462019-11341”,
“US_3d11e440 UCP_140”,
“US_3d11e440 UCP_139”
],
“ApplyingTransType”: [
“_vendorPayment”,
“_vendorCredit”,
“_vendorCredit”
]
}
]

Thanks

It looks like the document has the data spread across a few arrays. So, at some point, you’ll want to use a JSONSplitter to get your final result. But, first, you’ll need to combine the different arrays into one array that can be split. I think the following expression can be used to combine things together:

$BillStatus.map((elem, index) => {
  BillStatus: elem,
  ApplyingTransAmount: $ApplyingTransAmount[index],
  ApplyingTransDocNum: $ApplyingTransDocNum[index],
  ApplyingTransType: $ApplyingTransType[index]
})

That expression uses the Array.map() method to walk over the elements of the $BillStatus array and then produce a new array of objects with the values from the other arrays. Putting that expression in a mapper and then following that up with a JSONSplitter should get you what you want.

Here’s a pipeline that does as much:

Community-TransformJSONData_2020_01_29.slp (5.8 KB)

1 Like