โ11-28-2018 08:12 AM
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)
โ11-28-2018 09:40 AM
What do you mean by โflattenโ? Whatโs the expected output format? CSV?
โ11-29-2018 06:54 AM
The expected output is typical records to load into Oracle DB
โ11-28-2018 11:08 AM
Try the attached pipeline. It produces JSON in your flat arrangement.
JSON flatten_2018_11_28.slp (21.2 KB)
โ11-28-2018 12:31 PM
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)