Transform JSON Data

[skodali]

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

[tstack · Accepted solution]

skodali:

Please find the below JSON data and the expected output.
Can anyone suggest how can I achieve this?

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)