I want to Insert data from json file into mysql database using Nifi

[#1]

I have a JSON File that has 4 column fields like

[{"X": 1111, "Y": 2222, "Z": 3333, "W": "44444"},{"X": 55555, "Y": 6666, "Z": 7777, "W": "88888"},{"X": 9999, "Y": 12121, "Z": 232323, "W": "343434"},{"X": 454545, "Y": 565656, "Z": 676767, "W": "787878"}]

and i just want to insert that data into mysql database which is having different column name say A,B,C,D.

As both the column names are different so i am using replace text replacetext.png which is working fine if i use ------Search Value = X and Replace Value = A and is inserting the data into the table for all X values to A But I want to replace all my JSON fields to map with my database columns like X-A, Y-B, Z-C, W-D and i tried that with values provided in the screenshot but it is not working. Please if anybody could help me with this.

[#2]

Are you trying to retain the structure of the JSON Array and/or objects, and just rename the fields? If so, try the JoltTransformJSON processor. I am guessing you will eventually need to split the JSON array into individual objects in order to insert them into your database? If so, then try SplitJson first (with a JSON Path expression of $[*] or $.*) to get each JSON object into its own flow file. Then you can use JoltTransformJSON with the following Shift specification:

{
    "operation": "shift",
    "spec": {
      "X": "A",
      "Y": "B",
      "Z": "C",
      "W": "D"
    }
}

That should map your fields the way you have described (X=A, Y=B, Z=C, W=D). It may be possible to have a Shift or Chain specification that would handle the mapping for your entire original array, but I wasn't able to get a working spec (using this demo app), perhaps someone else (@Yolanda M. Davis ?) can chime in on that.

[#3]

Thanks @Matt Burgess for the help, I did what you mention but i am getting null after the transformation.Can you please tell me what am i doing wrong.jolttransformationjson.png

[#4]

Your input is an array but that specification works on a single JSON object. Try one of those objects at a time. And when you run your flow, make sure you have the SplitJson before the JoltTransformJSON processor, to divide up the array into individual flow files, each containing a single JSON object. As I said above, if you need to process the entire array at once, you will need a different specification, and I couldn't create one that worked.

[#5]

Thanks alot @Matt Burgess .It finally worked for me. But can we directly convert the column names coming from the CSV or any other files including JSON using replace text processor only ? so it would not take so much processing time, like X-A,Y-B,Z-C,W-D and insert directly into the database.

[#6]

ReplaceText uses regular expressions, so if you can write a regex that will successfully match the field names you want, you should be able to use that. I usually avoid that approach for structured text like JSON, especially if I don't know what the input will look like. But if you know the format/structure, you may find that ReplaceText works fine for your needs.