How do I flatten a JSON array to a csv file

Question

I have an incoming payload in JSON format where I am outputting some of the objects to a CSV file. The payload also has one array:

      "Chargebacks": [
        {
          "CostCenterCode": "123ABC",
          "AllocationPercentage": 100
        },
        {
            "CostCenterCode": "456DEF",
            "AllocationPercentage": 100
        }
      ]

I need the CSV file to contain:

<other headers from the objects>,Cost Center Code 1, Allocation Percentage 1, Cost Center Code 2, Allocation Percentage 2
<other object values>,123ABC,100,456DEF,100

My first attempt was to create two variables to hold the list of headers and list of values:

%dw 2.0
output application/csv
var x = payload.Item.CatalogAttributes.Chargebacks map (chargeBack, index) -> 
{
    "header": "Cost Center Code " ++ index+1 ++ ", Allocation Percentage "++ index+1,
    "costCenterCode": chargeBack.CostCenterCode ++ "," ++ chargeBack.AllocationPercentage,
}
var foo = x.*header joinBy ','
var bar = x.*costCenterCode joinBy ','
---

and add them to the end of the file:

foo: bar

and it kind of works. I get the value "foo" at the end of the headers and 123ABC\,100\,456DEF\,100 at the end of the values. How do I get the actual value of foo and remove the slashes from the values?

Michael Jones Michael Jones · Accepted Answer · 2021-01-14T20:41:37

I'm going to assume in my answer you might not know ahead of time how many chargeback items there are. This dataweave:

%dw 2.0
output application/csv
---
payload map {
    ($ - "Chargebacks"),
    ($.Chargebacks map {
        ("CostCenterCode_$($$)": $.CostCenterCode),
        ("AllocationPercentage_$($$)": $.AllocationPercentage)
    })
}

using this sample input:

[
    {
        "field1": "someValue",
        "field2": "someValue",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            }
        ]
    },
    {
        "field1": "someValue2",
        "field2": "someValue2",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC2",
                "AllocationPercentage": 200
            },
            {
                "CostCenterCode": "456DEF2",
                "AllocationPercentage": 200
            }
        ]
    }
]

produces this csv:

field1,field2,CostCenterCode_0,AllocationPercentage_0,CostCenterCode_1,AllocationPercentage_1
someValue,someValue,123ABC,100,456DEF,100
someValue2,someValue2,123ABC2,200,456DEF2,200

By wrapping our map in the (...) we are basically telling it to take the resulting array and flatten it into the top level object. This is very similar to the spread operator in javascript if you're familiar. The $ and $$ are shorthand for the function. Eg, if you have a function like this: fun someFun(left, fn: (item, index) -> Any), you can call it using this pattern payload someFun ..., where payload becomes the parameter left, and then the right hand side becomes the function; each parameter passed into the function becomes a $, where the number of $s is the position of the parameter. Make sense? Note that this pattern of calling functions isn't limited to one that takes a function. Eg, you could create a function like this: fun add(left, right) = left + right and call it this way 1 add 2. This only works when using the fun keyword, and when you have exactly two parameters.

If you're going to have potentially irregular sizes (ie: some might have more than others) and need to have the blank entries for smaller elements, you'll need to determine the max size ahead of time and do something like this:

%dw 2.0
output application/csv
var maxSize = max(payload map sizeOf($.Chargebacks))
---
payload map (row) -> {
    (row - "Chargebacks"),
    ((1 to maxSize) map {
        ("CostCenterCode_$($$)"): row.Chargebacks[$$].CostCenterCode,
        ("AllocationPercentage_$($$)"): row.Chargebacks[$$].AllocationPercentage
    })
}

That will map an input like this:

[
    {
        "field1": "someValue",
        "field2": "someValue",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            }
        ]
    },
    {
        "field1": "someValue2",
        "field2": "someValue2",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC2",
                "AllocationPercentage": 200
            },
            {
                "CostCenterCode": "456DEF2",
                "AllocationPercentage": 200
            }
        ]
    }
]

to this:

field1,field2,CostCenterCode_0,AllocationPercentage_0,CostCenterCode_1,AllocationPercentage_1,CostCenterCode_2,AllocationPercentage_2
someValue,someValue,123ABC,100,456DEF,100,456DEF,100
someValue2,someValue2,123ABC2,200,456DEF2,200,,

How do I flatten a JSON array to a csv file

1 Answers