Manipulating the keys and/or values of a nested JSON

[#1]

Hey guys,

is there a way of using existing NiFi processors to manipulate nested JSON-files that are mainly of the same structure and have like 10k+ lines? I´ve read a lot About the ReplaceText processor (Maybe JoltTransformJson?), but I could not get it to work. The main Problem is that I have to either change the key or the value, or sometimes even both.

So I am currently having a Model and am receiving data that has to get mapped to this. The only constant is the structure of the json and the keys. So there should be a way to transform them into the key names that are required.

{"id":"test"},{"id":"14"}

For example, if I get this JSON, I would have to transform "id" to "number" and "test" to "3", for example.

Is there a way of doing so without creating a new processor?

Sincerely,

J

[#2]

@Matt Burgess

[#3 · Accepted solution]

@Julez Nyzl

JSON Jolt is mainly used to modify the structure of the JSON document. The NiFi JSON processor also supports expression language.

For you specific case, I would recommend using a combination of both Json Path processor and Jolt processor

Step 1 : extract the "value" part from the document e.g "test" into a flow file variable using the json path processor

Step 2 : use jolt processor to transform the key "id" into "number" and for the vaule part use the nifi expression language to manipulate the flow file variable as you would need it.

Jolt spec to transform "id" into "number"

[
  {
    "operation": "shift",
    "spec": {
      "id": "number"
    }
  }
]

here is a link for How to use NiFi EL in jolt processor by @Yolanda M. Davis - https://community.hortonworks.com/articles/105965/expression-language-with-jolt-in-apache-nifi.html

[#4]

@Prabhu M Thanks for your Reply, I am really appreciating it. Is there a way - by using Json Jsolt - to go through the nested Array and differentiate between the same called keys in different nestings? for example there is an id directly in the data Array and an id in another Array that is inside the data Array?

Additionally, is there a possible way of using ExecuteScript? I tried something like this:

But I could not get it to work properly. This seems like the easiest attempt to me, but I do not know how to set/write a new key with the old value.

import org.apache.commons.io.IOUtils
import java.nio.charset.StandardCharsets
import groovy.json.*
import java.lang.*
import java.util.*
import java.io.*
import java.net.*
import groovy.lang.*
import groovy.util.*
import java.math.BigInteger
import java.math.BigDecimal
import testST.Deg2UTM
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)
 
          // Updade postal_code to PLZ and add the former value
    for(i in 0..obj.data.size()-1){
   def value = obj.data[0].remove("postal_code")
   obj.data[0].["PLZ"]=value
    }
          // 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)
}


<br>

[#5]

Yes you can access the same key names at different places in the JSON. Can you provide a sample input JSON and the expected output JSON?

[#6]

Hey @Matt Burgess , thanks for your reply. I got this issue solved. My new problem is explained in a new topic. I get for every Table a new Json that I have to match with the self-generated Json and put it in the DB with mutliple tables. And I do not know how to achieve this in a proper way.