Nifi Groovy Script to convert Json to CSV

1
votes

I have input json HTTP response in below format. I want to extract only few fields from Json and put them in CSV format. I want to write Groovy script to read json and write it to csv format.

Input json file:

{
  "Links": {},
  "Items": [
    {
      "Timestamp": "2019-11-20T01:51:11.8660125Z",
      "Value": 36.77309,
      "UnitsAbbreviation": "",
      "Good": true,
      "Questionable": false,
      "Substituted": false
    },
    {
      "Timestamp": "2019-11-20T02:17:19.7750091Z",
      "Value": 36.8910828,
      "UnitsAbbreviation": "",
      "Good": true,
      "Questionable": false,
      "Substituted": false
    }
  ],
  "UnitsAbbreviation": "AHK"
}

Expected Output csv format:

Timestamp,Value
"2019-11-20T01:51:11.8660125Z",36.77309
"2019-11-20T02:17:19.7750091Z",36.8910828

Can anyone help me with sample Groovy code as I am new to Groovy? I dont want to complicate the flow with multiple process like splitting the json, evaluate json and write to csv.

Any help or pointers would be appreciable. Thanks.

2
groovyapache-nifi

2 Answers

2
votes

Using ExecuteScript to avoid using the other processors is pretty much missing the point of NiFi.

You don't have to use SplitJSON or EvaluateJSONPath either.

You could, instead, use JoltTrasnformJSON with Jolt Transformation DSL set as Shift and Jolt Specification set as:

{
    "Items": {
        "*": {
            "Timestamp": "[#2].&",
            "Value": "[#2].&"
        }
    }
}

Which would transform your JSON into your desired schema in JSON format. Then, use ConvertRecord with Record Reader set to a JsonTreeReader controller service and Record Writer set to a CSVRecordSetWriter controller service.

So we only have here ->JoltTransformJSON->ConvertRecord->

This would convert your input into your wanted outcome with two processors that are guaranteed to work.

Using ExecuteScript is risking unknown behaviors and bugs.

1
votes

Not sure if it has something to do with nifi, but in plain groovy your code can look like:

String inp = '''{
      "Links": {},
      "Items": [
        {
          "Timestamp": "2019-11-20T01:51:11.8660125Z",
          "Value": 36.77309,
          "UnitsAbbreviation": "",
          "Good": true,
          "Questionable": false,
          "Substituted": false
        },
        {
          "Timestamp": "2019-11-20T02:17:19.7750091Z",
          "Value": 36.8910828,
          "UnitsAbbreviation": "",
          "Good": true,
          "Questionable": false,
          "Substituted": false
        }
      ],
      "UnitsAbbreviation": "AHK"
    }'''

Map json = new groovy.json.JsonSlurper().parseText inp

String csv = json.Items.inject( 'Timestamp,Value' ){ res, item -> res + """\n"$item.Timestamp",$item.Value""" }

assert csv == '''Timestamp,Value
"2019-11-20T01:51:11.8660125Z",36.77309
"2019-11-20T02:17:19.7750091Z",36.8910828'''