Parsing JSON to CSV in Groovy

Question

I'm trying to parse json file to csv. Could you help? example json:

{
   "expand": "schema,names",
   "startAt": 0,
   "maxResults": 50,
   "total": 21,
   "issues":    [
            {
         "expand": "operations,versionedRepresentations",
         "id": "217580",
         "self": "issue/217580",
         "key": "ART-4070",
         "fields": {"summary": "#[ART] Pre.3 Verification \\"S\\""}
      },
            {
         "expand": "operations,versionedRepresentations",
         "id": "217579",
         "self": "issue/217579",
         "key": "ART-4069",
         "fields": {"summary": "Verification \\"C\\""}
      },
            {
         "expand": "operations,versionedRepresentations",
         "id": "217577",
         "self": "issue/217577",
         "key": "ART-4068",
         "fields": {"summary": "#[ART] Enum type"}
      }
   ]
}

result csv should be like:

key;summary
ART-4070;#[ART] Pre.3 Verification \"S\"
ART-4069;Verification \"C\"
ART-4068;#[ART] Enum type

I've tried such a code:

import groovy.json.*

def jsonSlurper = new JsonSlurper()
def json = '''
{
   "expand": "schema,names",
   "startAt": 0,
   "maxResults": 50,
   "total": 21,
   "issues":    [
            {
         "expand": "operations,versionedRepresentations",
         "id": "217580",
         "self": "issue/217580",
         "key": "ART-4070",
         "fields": {"summary": "#[ART] Pre.3 Verification \\"S\\""}
      },
           {
         "expand": "operations,versionedRepresentations",
         "id": "217579",
         "self": "issue/217579",
         "key": "ART-4069",
         "fields": {"summary": "Verification \\"C\\""}
      },
            {
         "expand": "operations,versionedRepresentations",
         "id": "217577",
         "self": "issue/217577",
         "key": "ART-4068",
         "fields": {"summary": "#[ART] Enum type"}
      }
   ]
}
'''
def obj = jsonSlurper.parse(json)

def columns = obj.issues*.keySet().flatten().unique()

// remove nulls
def encode = { e -> e == null ? '' : e  }

// Print all the column names
println columns.collect { c -> encode( c ) }.join( ';' )

// create all the rows
println obj.issues.collect { row ->
    // A row at a time
    columns.collect { colName -> encode( row[ colName ] ) }.join( ';' )
}.join( '\n' )

but result is wrong:

expand;id;self;key;fields operations,versionedRepresentations;217580;issue/217580;ART-4070;[summary:#[ART] Pre.3 Verification "S"] operations,versionedRepresentations;217579;issue/217579;ART-4069;[summary:Verification "C"] operations,versionedRepresentations;217577;issue/217577;ART-4068;[summary:#[ART] Enum type]

how can i extract only what i want from json file? I need only two columns:key,summary and values for them.

score 1 · Accepted Answer · answered Sep 04 '20 at 12:43

1

You want to extract only specific information from your list of issues and you need different strategies to extract those. So I'd use a "configuration" to describe the extraction (see the map config below). Then the code is quite close to your original one (extracted some common code etc)

import groovy.json.*

def config = [ // header -> extractor
    "key": { it.key },
    "summary": { it.fields.summary }
]

def encode(e) { // help with nulls; quote the separator
    (e ?: "").replaceAll(";", "\\;")  
}

def csvLine(items) { // write items as "CSV"
    println(items.collect{ encode it }.join(";"))
}

// main
def obj = new JsonSlurper().parse("data.json" as File)
csvLine(config.keySet())
obj.issues.each{ issue ->
    csvLine(config.values().collect{ f -> f issue })
}

answered Sep 04 '20 at 12:43

cfrick

35,203
6
56
68

Thank you, how can I save result in a csv file? – rafal.sza Sep 04 '20 at 13:50
1

https://stackoverflow.com/questions/23410973/how-to-append-text-to-a-file-on-a-new-line – cfrick Sep 05 '20 at 07:11
how can I save result e.g. in File csvFile = new File( 'C:/temp/all1.csv') ? – rafal.sza Sep 07 '20 at 08:04

Parsing JSON to CSV in Groovy

1 Answers1