Mulesoft transform multiple JSON/XML to CSV

Question

I'm totally new on transforming JSON to CSV in Mulesoft. Currently i need to work on to transform a nested JSON into CSV which my CSV should look like this and my JSON will be like

{
  "Invoice": [
    {
      "Invoice Number*": "",
      "Supplier Name": "",
      "Supplier Number": "",
      "Status": "",
      "Invoice Date*": ""
    }
  ],
  "Invoice Line": [
    {
      "Invoice Number*": "",
      "Supplier Name": "",
      "Supplier Number": "",
      "Line Number": "",
      "Description*": "",
      "Supplier Part Number": "",
      "Auxiliary Part Number": "",
      "Price*": "",
      "Quantity": "",
      "Bulk Price": "",
      "Bulk Price Qty": ""
    }
  ],
  "Invoice Tax Line": [
    {
      "Invoice Number*": "",
      "Invoice Line Number": "",
      "Invoice Charge Number": "",
      "Line Number": "",
      "Tax Amount": "",
      "Tax Rate": "",
      "Tax Code": "",
      "Tax Rate Type": ""
    }
  ]
}

What i know about CSV is there will be only one header. What is the best way for me to map this complex JSON into CSV which will shows different header?

aled aled · Accepted Answer · 2019-08-30T11:29:06

You can do something like this:

%dw 2.0
output application/csv header=false
---
flatten (
    [
        {"Invoice Number":"Invoice Number",
            "Supplier Name": "Supplier Name",
            "Supplier Number" :"Supplier Number",
            "Status" : "Status",
            "Invoice Date*" : "Invoice Date*"
        },
        {
            "Invoice Number":"Invoice Number*",
            "Supplier Name":"Supplier Name",
            "Supplier Number":"Supplier Number",
            "Status":"Status",
            "Invoice Date*":"Invoice Date*"
        },
        payload.Invoice map {
            "Invoice Number":$."Invoice Number*",
            "Supplier Name":$."Supplier Name",
            "Supplier Number":$."Supplier Number",
            "Status":$.Status,
            "Invoice Date*":$."Invoice Date*"
        }, 
        payload."Invoice Line" map {
            "Invoice Number": $."Invoice Number*",
            "Supplier Name":$."Supplier Name",
            ...
        } 
        , 
        payload."Invoice Tax Line" map {
            "Invoice Number":$."Invoice Number*",
            ...
        } 
    ]
)

Mulesoft transform multiple JSON/XML to CSV

1 Answers