Groovy script for Json to CSV conversion

Question

I need to convert the below json to CSV using groovy script. My input Json is below

[{
"id":1,
"campaign":
{
"cost":0,
"name":"Test1"
},
"visitorId":3
},
{
"id":2,
"campaign":
{
"cost":0,
"name":"Test2"
},
"visitorId":5
}]

and i need the out put in the below csv format

id,cost,name,visitorID
1,0,Test1,3
2,0,Test2,5

I am using below groovy script but it gives a different output

def jsonSlurper = new groovy.json.JsonSlurper();
def object = jsonSlurper.parseText(message.getBody(java.lang.String) as String)
StringBuilder sb = new StringBuilder()
object.each{
    sb << it.collect{ it.value }
            .join(",")
            .concat("\n")}
message.setBody(sb.toString())

Any guidance would be highly appreciated

Answer 1

Your object is a List of nested Maps. If you want it to be kind of generic and you can rely on this specific structure you could try something like

def jsonSlurper = new groovy.json.JsonSlurper()
List<Map> object = jsonSlurper.parseText(message.getBody(java.lang.String) as String)
List<List<Map.Entry>> rows = object.collect{ Map map -> map.collectMany{ k, v -> v instanceof Map ? v.entrySet() : [Map.entry(k, v)] } }
List header = rows.first().collect { it.key }
List<List> body = rows.collect { row -> row.collect { it.value } }
String csvOutput = ([header] + body).collect { List row -> row.join(",") }.join("\n")

but you better listen to chubbsondubs' advice.