Apache Nifi manipulate nested json with Execute Script

Question

I am looking for a way to update the given Json's values and keys in a dynamic way. The way the Json is delivered is Always the same(in Terms of structure). The only Thing that differs is the amount of Data that is provided. So for example there could sometimes be 30, sometimes only 10 nestings etc.

…

"ampdata": [
                {
                    "nr": "303",
                    "code": "JGJGh4958GH",
                    "status": "AVAILABLE",
                    "ability": [ "" ],
                    "type": "wheeled",
                    "conns": [
                        {
                            "nr": "447",
                            "status": "",
                            "version": "3",
                            "format": "sckt",

                            "amp": "32",
                            "vol": "400",
                            "vpower": 22

                        }
                    ]
                }

As Json uses other keys/values than I in my DB, I Need to convert them. Additionally I Need to Change some values if they match explicit strings. So for example: "Code" has to be renamed to"adrID" and "sckt" should map to the values "bike".

I tried a simple Groovy-Script to remove the key and or Change the value. There is no Problem in changing values, but in changing the key itself. So I tried removing the key and adding a new key. Unfortunately I could not figure out how to add a new key:value to the given json. So how can I add a new pair of key:value or rename the key, if that´s possible. Have a look at my code-example

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

try {
  flowFile = session.write(flowFile,
      { inputStream, outputStream ->
          def text = IOUtils.toString(inputStream, StandardCharsets.UTF_8)
          def obj = new JsonSlurper().parseText(text)
          def objBuilder = new JsonBuilder(obj)

          // Update ingestionDate field with today's date
          for(i in 0..obj.data.size()-1){
            obj.data[0].remove("postal_code")
            objBuilder.data[0].postal_code=5

          }
          // Output updated JSON
          def json = JsonOutput.toJson(obj)
          outputStream.write(JsonOutput.prettyPrint(json).getBytes(StandardCharsets.UTF_8))
      } as StreamCallback)
  flowFile = session.putAttribute(flowFile, "filename", flowFile.getAttribute('filename').tokenize('.')[0]+'_translated.json')
  session.transfer(flowFile, REL_SUCCESS)

} catch(Exception e) {
  log.error('Error during JSON operations', e)
  session.transfer(flowFile, REL_FAILURE)
}

what have you tried so far? what's your question? please provide input and output json examples.... — daggett
I tried a simple Groovy-Script to remove the key and or Change the value. There is no Problem in changing values, but in changing the key itself. So I tried removing the key and adding a new key. Unfortunately I could not figure out how to add a new key:value to the given json. So how can I add a new pair of key:value or rename the key, if that´s possible. Have a look at my code-example — Steve

daggett daggett · Accepted Answer · 2018-11-21T16:26:41

...
def obj = new JsonSlurper().parse(inputStream, "UTF-8")
obj.data.each{e->
    def value = e.remove("postal_code")
    //set old value with a new key into object
    e["postalCode"] = value
}
//write to output
def builder = new JsonBuilder(obj)
outputStream.withWriter("UTF-8"){ it << builder.toPrettyString() }

Apache Nifi manipulate nested json with Execute Script

1 Answers