Unable to transform Complex JSON Object to CSV using Dataweave

0
votes

Need some guidance or hint in transforming this complex json object to csv. I personally feel that my input data is incorrect or not proper which I designed from my previous flows and obtained this as a result. Any suggestions in modifying my input json object and obtaining the desired result will also be helpful for me.

Note - As here in this example there is one APD in each API, similarly there are many API and APD. Just attached a part of my Json Object.

Input Json Object

{
    "API": {
        "VendorID": "39594",
        "InvoiceName": "December Expenses (12/01/2020)",
        "Description": "HectorEchiveste",
        "SumInvoiceAmount": 622.02,
        "InvAcctDate": "1222021",
        "InvAcctDate2": "1222021",
        "APD": {
            "JobNumber": [
                "",
                "C8D-27-14387",
                "",
                "",
                "CL5-27-11934",
                "CL5-27-11934",
                "",
                "",
                "",
                ""
            ],
            "CostCode": [
                "",
                "1000",
                "",
                "",
                "1000",
                "1000",
                "",
                "",
                "",
                ""
            ],
            "CostCategory": [
                "",
                "M",
                "",
                "",
                "O",
                "O",
                "",
                "",
                "",
                ""
            ],
            "GL": [
                "1-20-70-6600",
                "1-20-70-5200",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-5500",
                "1-20-70-5500",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-6600"
            ],
            "Amount": [
                "60.00",
                "166.17",
                "51.34",
                "60.00",
                "67.75",
                "9.00",
                "45.00",
                "48.75",
                "50.00",
                "64.01"
            ]
        }
    },
    "API": {
        "VendorID": "39849",
        "InvoiceName": "December Expenses (12/01/2020)",
        "Description": "DonaldOrtega",
        "SumInvoiceAmount": 223.08,
        "InvAcctDate": "1252021",
        "InvAcctDate2": "1252021",
        "APD": {
            "JobNumber": [
                "B8G-87-15555"
            ],
            "CostCode": [
                "1000"
            ],
            "CostCategory": [
                "M"
            ],
            "GL": [
                "1-80-70-5200"
            ],
            "Amount": [
                "223.08"
            ]
        }
    }

Required Csv format csv format

2
jsonmuledataweavemulesoft
You are missing a trailing } in your input data. You also have a JSON object with repeating fields which is NOT a good practice. I will definetelly, start reconsidering the outer-most object into an array. Furthermore, why have arrays for Ammounts etc. You should have a single object associating the details of a Vendor as compared using the array index to do it. - George

2 Answers

0
votes

Can you check your payload? I'm getting this error.

Unexpected character ']' at payload@[96:6] (line:column), expected '}'
0
votes

Here's something quick and dirty I wrote based upon your current input format:

%dw 2.0
output application/csv

var data = {
    "API": {
        "VendorID": "39594",
        "InvoiceName": "December Expenses (12/01/2020)",
        "Description": "HectorEchiveste",
        "SumInvoiceAmount": 622.02,
        "InvAcctDate": "1222021",
        "InvAcctDate2": "1222021",
        "APD": {
            "JobNumber": [
                "",
                "C8D-27-14387",
                "",
                "",
                "CL5-27-11934",
                "CL5-27-11934",
                "",
                "",
                "",
                ""
            ],
            "CostCode": [
                "",
                "1000",
                "",
                "",
                "1000",
                "1000",
                "",
                "",
                "",
                ""
            ],
            "CostCategory": [
                "",
                "M",
                "",
                "",
                "O",
                "O",
                "",
                "",
                "",
                ""
            ],
            "GL": [
                "1-20-70-6600",
                "1-20-70-5200",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-5500",
                "1-20-70-5500",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-6600",
                "1-20-70-6600"
            ],
            "Amount": [
                "60.00",
                "166.17",
                "51.34",
                "60.00",
                "67.75",
                "9.00",
                "45.00",
                "48.75",
                "50.00",
                "64.01"
            ]
        }
    },
    "API": {
        "VendorID": "39849",
        "InvoiceName": "December Expenses (12/01/2020)",
        "Description": "DonaldOrtega",
        "SumInvoiceAmount": 223.08,
        "InvAcctDate": "1252021",
        "InvAcctDate2": "1252021",
        "APD": {
            "JobNumber": [
                "B8G-87-15555"
            ],
            "CostCode": [
                "1000"
            ],
            "CostCategory": [
                "M"
            ],
            "GL": [
                "1-80-70-5200"
            ],
            "Amount": [
                "223.08"
            ]
        }
    }
}

---
flatten(data.*API map (vendor) -> (
    vendor.APD.CostCode map (apds,idx) -> do {
        var common = vendor - "APD"
        var uncommon = vendor.APD pluck $$
        ---
        {
            (common),
            (uncommon reduce (e, acc={}) -> acc ++ {(e): vendor.APD[e][idx]})
        }
    }
))

I can simplify the code when and if I get some extra time. For example, if I rework the expression the outermost flatten can be skipped.

If I were you I would prefer if the input data looked as follows:

[
    {
        "VendorID": "39594",
        "InvoiceName": "December Expenses (12/01/2020)",
        "Description": "HectorEchiveste",
        "SumInvoiceAmount": 622.02,
        "InvAcctDate": "1222021",
        "InvAcctDate2": "1222021",
        "APD": [
            {
                "JobNumber": "",
                "CostCode": "",
                "CostCategory": "",
                "GL": "1-20-70-6600",
                "Amount": "60.00"
            },
            {
                "JobNumber": "C8D-27-14387",
                "CostCode": "1000",
                "CostCategory": "M",
                "GL": "1-20-70-5200",
                "Amount": "166.17"
            },
            /// .... and so on          
        ]
    },
    {
      // ... and so on  
    }
]