About jnoichl

jnoichl · ‎11-28-2018

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.

jnoichl · ‎11-27-2018

As first step I looked up the DB and with ExecuteSQL and converted the Avro-File I got to a Json-File using the ConvertAvroToJson processor. Now I am having two Json-Files that have a lot of same and a lot of different keys. The self-generated Json - as presented above - is nested and the Json-File from the DB is flat, as I am extracting the relevant DB-Information step-by-step. I'd like to use ExecuteScript with JavaScript now, as I would like to merge both Jsons in that way that every matching key gets the value of the self-generated Json and every missing key(the DB-Json has a lot more keys) is added to the self-generated Json. My problem is now especially that I do not know how to read two seperate Jsonfiles with Javascript/Executescript without overwrite them. Furthermore I do not know how to seperate the incoming Flowfiles (detect if they are coming from one or another processor). It would be nice if you could give me any suggestions. @Prabhu M

jnoichl · ‎11-26-2018

Is there a way of looking up my DB with multiple tables, extracting the rows in a JSON and compare them to another JSON that I generated in another flow? The generated JSON has pretty much the same name for keys as the database column-names. The only difference is that my self-generated JSON is nested and therefore contains a lot arrays. How can I compare matching names(key- and columnames) from the nested and flat-Json I am having? I am currently using the Execute SQL-Processor which gives me AVRO-formatted Data that I transformed with ConvertAvrotoJSON to a JSON file that is flat. My second problem is that I would like to update the self-generated JSON with missing values from the DB and put it again into the DB/update the existing values in the DB. I tried using the ConvertJSON-Processor which is unfortunately designed for only one table, when I do have like 50 tables. Is there an easy yet efficient way of doing the desired things? The self-generated JSON looks like this: "ampdata": [ { "nr": "303", "code": "JGJGh4958GH", "status": "AVAILABLE", "ability": [ "" ], "type": "wheeled", "conns": [ { "nr": "447", "status": "", "version": "3", "format": "sckt", "amp": "32", "vol": "400", "vpower": 22 } ] } The DB data is pretty much the same, but flat and contains sometimes more information that are missing in the self-generated JSON. This information has to be added to the self generated JSON. The updated JSON should update the current DB-entry. @Matt Burgess If you require more information, please let me know.

jnoichl · ‎11-22-2018

@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>

jnoichl · ‎11-21-2018

@Matt Burgess

jnoichl · ‎11-16-2018

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

jnoichl · ‎11-14-2018

Hey guys, I am currently facing an issue related to the nested JSON-Date I am trying to process. My current NiFi flow is: GetFile => SplitJson => ConvertJSONtoSQL => PutSQL. Everything seems to be quite fine, but it seems that there are Problems with handling nested JSONs. My quick Research brought up two possibilities: using JoltTransformJSON or an ExecuteScript. I'd rather prefer the first one. But I do not know, actually, how to achieve the Right and desired Transformation of the nested JSON into a flat one. Hope you can give me some hints. Regards, Julez

Online	Offline
Last Visited	‎12-04-2018 02:46 PM

Member Since	‎11-13-2018 08:22 AM
Last Visited	‎12-04-2018 02:46 PM
Posts	9

Cloudera Community

Re: Manipulating the keys and/or values of a neste...

Re: Looking up database and compare two JSON-Files

Looking up database and compare two JSON-Files

Re: Manipulating the keys and/or values of a neste...

Re: Manipulating the keys and/or values of a neste...

Manipulating the keys and/or values of a nested JS...

Transform a nested JSON into SQL and insert it in ...