Splitting concatenated values in table column using Dataweave

Question

I have sample data in my Salesforce table as given below:

I am on Mule 3.9 and running dataweave 1.0. I need to use dataweave to read the above data (from a Salesforce table) and transform it into a JSON as given below:

[
      {"Id": "634594cc","Name": "Alpha","List": "AB01"},
      {"Id": "634594cc","Name": "Alpha","List": "AB02"},
      {"Id": "634594cc","Name": "Alpha","List": "AB03"},
      {"Id": "5d839e9c","Name": "Bravo","List": "CD01"},
      {"Id": "5d839e9c","Name": "Bravo","List": "CD02"},
      {"Id": "3a5f34d3","Name": "Charlie","List": null}
] 

As you can see above, the "List" column is what I need to split as separate arrays in the final JSON. It has data with semicolon at the begin, in between and in the end.

Thanks in advance for your help.

Answer 1

I took the liberty to create a couple of sample data based upon the SS (BTW, its best not to use SS) :).

Try this:

%dw 1.0
%output application/dw
%var data = [
    {
        id: "ABC123",
        name: "A",
        list: ";AB1;AB2;AB3;"
    },
    {
        id: "ZXY321",
        name: "B",
        list: null
    }
]
---
data reduce (e,result=[]) -> (
    result ++ using (
        list = e.list default "" splitBy /;/ filter ($ != ""),
        sharedFields = {
            Id: e.id,
            Name: e.name
        }
    ) (
        using (
            flist = list when ((sizeOf list) > 0) otherwise [null]
        ) (
            flist map {
                (sharedFields),
                List: $
            }
        )

    )
)