Flattening multiple array entities in JSON Object

Hello,

I am trying to flatten multiple array attributes from a JSON Document. Below is the JSON document and the expected output.

{
“TransactionId”: 55499,
“ReceiptNo”: 1123,
“Location”: “xxxx-xxxxx”,
“Total”: 8.08,
“TotalGross”: 7.46,
“TransactionMedias”: [
{
“MediaId”: 99862,
“MediaNo”: 6,
“MediaValue”: 8.08,
},
{
“MediaId”: 99863,
“MediaNo”: 498,
“MediaValue”: 0.62,
}
],
“TransactionItems”: [
{
“ItemID”: 356391,
“ProductID”: 113900,
“Quantity”: 1,
“Total”: 5.94,
“TotalGross”: 5.49,
“TotalTax”: 0.45,
},
{
“ItemID”: 356392,
“ProductID”: 100097,
“Quantity”: 1,
“Total”: 0,
“TotalGross”: 0,
“TotalTax”: 0,
},
{
“ItemID”: 356393,
“ProductID”: 100097,
“Quantity”: 1,
“Total”: 0.64,
“TotalGross”: 0.59,
“TotalTax”: 0.05,
}
}

I have attached an Excel file with the source JSON Document and the expected output in 2 separate tabs.

Thanks
Aravind NMulti_Array.xlsx (9.7 KB)

What do you mean by “flatten”? What’s the expected output format? CSV?

Try the attached pipeline. It produces JSON in your flat arrangement.

JSON flatten_2018_11_28.slp (21.2 KB)

Actually, here’s a much simpler pipeline to accomplish the same. Note that both of these pipelines resolve the name conflict between the “Total” and “TotalGross” fields in the root object vs the TransactionItem objects by renaming either the child fields (first pipeline) or the root fields (second pipeline).
JSON flatten 2_2018_11_28.slp (13.9 KB)

1 Like

The expected output is typical records to load into Oracle DB

You could also concatenate the two arrays together and then use a single JsonSplitter on the resulting array:

$TransactionItems.concat($TransactionMedias)

The Copy/Union can also be removed then as well.

1 Like

Here’s a pipeline using Tim’s tip:

JSON flatten 3_2018_11_29.slp (10.9 KB)

1 Like

Thank you for the Solution. That works and helps.

Thanks a lot. Nice to learn to concat the arrays and manipulate them. The attached pipeline is exactly what i needed. Thanks again.