Support Questions

Find answers, ask questions, and share your expertise

Looking up database and compare two JSON-Files

avatar
Explorer

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.

1 REPLY 1

avatar
Explorer

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