NiFi - Script for JSON-content manipulation before JOLT

[#1]

Hi all,

as far as I investigated this (https://community.hortonworks.com/questions/247330/nifi-need-help-with-jolt-syntax-jolttransformjson-1.html) it is not possible to do this with JOLT.

Now I want to manipulate the JSON-content BEFORE JOLT does simple transformation.

I'm getting used to do some things with groovy, viewed some examples but couldn't find the right syntax.

There are two things I need to to:

1. All timestamps from JSON have to be converted and formated from CET yyyyMMddHHmmss ==> to UTC yyyy-MM-dd HH:mm:ss.SSS

2. Some string manipulation with some kind of expression language doing this:

Expression Language to build "dnr_group"

   ${DNR:substringAfter('('):substringBefore(')'):toNumber()
                            :lt( 10 )
                                     :ifElse(${DNR:substringAfter('('):substringBefore(')'):prepend('0')},
                                             ${DNR:substringAfter('('):substringBefore(')')})
                            :append('-'):append(${GROUP:toNumber()
                            :lt( 10 )
                                     :ifElse(${GROUP:prepend('0')},
                                             ${GROUP})})
    :prepend('V-')}

Input-JSON

{
  "user": "Justen",
  "stand": "20190702121621",
  "table": [
    {
      "zn": 1,
      "elem_user": "Meier",
      "elem_stand": "20190705081410",
      "dnr": "(0)",
      "group": "1"
    },
    {
      "elem_stand": "20190706201918",
      "zn": 2,
      "elem_user": "Schmidt",
      "dnr": "(0)",
      "group": "2"
    }
  ]
}

Desired Output-JSON

    {
        "user": "Justen",
        "stand": "2019-07-02 10:16:21",
        "table": [
            {
                "zn": 1,
                "elem_user": "Meier",
                "elem_stand": "2019-07-05 06:14:10",
                "dnr_group": "V-00-01"
            },
            {
                "elem_stand": "2019-07-06 18:19:18",
                "zn": 2,
                "elem_user": "Schmidt",
                "dnr_group": "V-00-02"
            }
        ]
    }

Is it possible to do this with groovy or does anybody know other ways manipulating JSON-content in NiFi?

Thanks for any help!

[#2]

you could first read that line from the json file (evaluate json path) and then update attribute with expression language and push it back into the json file with update record processor.

let me know if you need further help.

[#3]

Hi @Rosa Negra thanks for your answer!

I worked with ff-attributes as long as the JSON-file contained only one entry at a time and it worked fine.

Now with the complex structure I couldn't find a way to manipulate the fields of the positions.

Unfortunately I have no experience with record processing. If I understand you correctly it would be possible to update the JSON doing record processing.

So I am "afraid" I have to explore it...

Maybe there are further solutions?

[#4]

Hi @justenji ! Please take a look at the below code and tell me if it is working or if you need any further upgradations. As of now, I have converted the timestamps and added dnr_group.

import java.nio.charset.StandardCharsets
import org.apache.nifi.components.PropertyValue
import groovy.json.JsonSlurper
import groovy.json.JsonOutput

flowFile = session.get()
if (!flowFile) return

try {
 def jsonSlurper = new JsonSlurper();
 def jsonOutput = new JsonOutput();

 def input = flowFile.read().withStream {
  data -> jsonSlurper.parse(data)
 }

 def pattern1 = 'yyyyMMddHHmmss';
 def tz1 = 'GMT+0200';
 def pattern2 = 'yyyy-MM-dd HH:mm:ss';
 def tz2 = 'GMT';

 input.stand = convertDatePattern(input.stand,pattern1,TimeZone.getTimeZone(tz1),pattern2,TimeZone.getTimeZone(tz2));

 for(int i=0;i<input.table.size();i++){
  input.table[i].elem_stand = convertDatePattern(input.table[i].elem_stand,pattern1,TimeZone.getTimeZone(tz1),pattern2,TimeZone.getTimeZone(tz2));
  def dnr = input.table[i].dnr.replaceAll('\\(|\\)','');
  def group = input.table[i].group.replaceAll('\\(|\\)','');
  if(dnr.toInteger() < 10){
   dnr = '0'+dnr;
  }
  if(group.toInteger() < 10){
   group = '0'+group;
  }

  input.table[i].dnr_group = "V-"+dnr+"-"+group;
  input.table[i].remove('dnr');
  input.table[i].remove('group');
 }

 flowFile = session.write(flowFile, {
  outputStream ->
  outputStream.write(jsonOutput.toJson(input).toString().getBytes(StandardCharsets.UTF_8))
 }as OutputStreamCallback);

 session.transfer(flowFile, REL_SUCCESS);
} catch (e) {
 log.error('Error Occured,{}', e)
 session.transfer(flowFile, REL_FAILURE)
}

def convertDatePattern(String input, String pattern1, TimeZone tz1, String pattern2, TimeZone tz2){
 return new Date().parse(pattern1,input,tz1).format(pattern2,tz2).toString();
}