Map nested json array into CSV

Question

I am trying to map an array payload into CSV in Dataweave and not able to achieve the result.

The csv will not need header, the content in the array will print in column by column. I'm facing problem to make the mapping through the nested array.

Input payload

[
  {
  "Invoice": {
    "Invoice Number*": "Test",
    "Supplier Number": "1201",
    "Submit For Approval?": "Yes",
    "Invoice Date*": "20190828",
    "Line Level Taxation*": "Yes",
    "Payment Date": "00/00/0000",
    "Original invoice number": "",
    "Original invoice date": ""
  },
  "Invoice Line": [
    {
      "Invoice Number*": "Test1",
      "Line Number": "1",
      "Description*": "Test1",
      "Price*": "500",
      "Quantity": null,
      "Unit of Measure*": null,
      "PO Number": "001",
      "PO Line Number": "1"
    },
    {
      "Invoice Number*": "Test2",
      "Line Number": "2",
      "Description*": "Test2",
      "Price*": "500",
      "Quantity": null,
      "Unit of Measure*": null,
      "PO Number": "001",
      "PO Line Number": "2"
    }
  ],
  "Invoice Tax Line": [
     {
       "Tax Amount": "500",
       "Invoice Line Number": "1",
       "Line Number": "1"
     },
     {
       "Tax Amount": "50",
       "Invoice Line Number": "2",
       "Line Number": "2"
     }
  ]
  }
]

Expected Output

column_0, column_1, column_2 ... //no header
"Invoice Number*","Supplier Number","Submit For Approval?"... //Invoice
"Invoice Number*","Line Number*"...                        //InvoiceLine[0]
"Tax Amount","Invoice Line Number","Line Number"...        //Tax Line[0]
"Invoice Number*","Line Number*"...                        //InvoiceLine[1]
"Tax Amount","Invoice Line Number","Line Number"...        //Tax Line[1]

How can i write the dataweave mapping to archive the result like above?

machaval machaval · Accepted Answer · 2019-09-05T17:05:03

This is the solution I found for your use case. Basically there are two functions to that dispatches on the right method according to the type. And then you also want to use zip function to mix one "Invoice Line" with one "Invoice Tax Line" so they are mixed correctly.

%dw 2.0
output application/csv headers=false 
import * from dw::core::Objects

fun collectKeyNames(obj: {}): Array<{}> = 
            [
                obj
            ]

fun collectKeyNames(arr: Array): Array<{}> = 
    arr flatMap ((obj, index) ->  collectKeyNames(obj))

---
payload flatMap ((item, index) -> 
    collectKeyNames(item.Invoice) ++ 
        (collectKeyNames(item."Invoice Line" zip item."Invoice Tax Line"))
)

Map nested json array into CSV

1 Answers