Dynamic CSV with Header only once

Question

I have a request to parse a JSON payload and then create columns dynamically based on a condition.

There should be a Groups column with header. For any additional groups the employee is in, they will be in a column with no header.

If the member is in one group it makes sense I can do something like the following:

%dw 2.0
output application/csv separator=","
var employeesPayload = payload
---
employeesPayload filter ($.workEmail != '' and $.companyEmploymentType.isContractor == false) map (employee) -> {
    "Employee ID":  employee.employeeNumber,
    "Name": employee.preferredFirstName default employee.firstName ++ ' ' ++ if (employee.preferredLastName == '' or employee.preferredLastName == null) employee.lastName else employee.preferredLastName,
    "Email": employee.workEmail,
    "Groups": employee.workState
}

i.e the table should look similar to the following:

But, how do I add additional columns without headers?

i.e if I want to add a user like Tito (row 9) in the screenshot, how can I build this dynamically?

Answer 1

You can add additional fields dynamically by mapping the payload. If you want the header to be empty you can set the key to an empty string. Note that you can not skip columns, if there is no content you need to at least output an empty string.

Example:

%dw 2.0
output application/csv
---
payload map {
    ($), // just reusing the input payload as is
    d: $$, // some calculated field with a header
    "": if (isEven($$)) $$ else "", // calculated field with an empty name, only on some condition
    "": $$ // another calculated field with an empty name 
}

Input:

[
    {
        "a": "a1",
        "b": "b1",
        "c": "c1"
    },
    {
        "a": "a2",
        "b": "b2",
        "c": "c2"
    },
    {
        "a": "a3",
        "b": "b3",
        "c": "c3"
    }
]

Output:

a,b,c,d,,
a1,b1,c1,0,0,0
a2,b2,c2,1,,1
a3,b3,c3,2,2,2