perform Regex on JSON data across each row and push into the MySQL table using NiFi

Question

I want to achieve the following use case:

I have a file as follows:

 enter code here
{"FirstName":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{"HouseName":67783,"Lastname":"Stevenson","Address":"Abel St"}
{"FoodName":7473,"Lastname":"luther","Address":"Half Moon Bay"}

si I want to add "NAME" and "Value" tag in the first column across each row so I can easily push all the FirstName, HouseName, and FoodName attributes under one column in MySQL named 'Name' and its respective values under "Value" column in MySQL. for example I want the data to look like as following:

{NAME:"FirstName","Value":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{NAME:"HouseName","Value":67783,"Lastname":"Stevenson","Address":"Abel St"}
{NAME:"FoodName","Value":7473,"Lastname":"luther","Address":"Half Moon Bay"}

My table in MySQL is as follows: Name Value Lastname Address

I am using the following flow:

GetFile->SplitRecord->ConvertJsonToSQL ->PutSQL

I want under the NAME column all the first column attribute names of each row(FirstName, HouseName, FoodName) and under Value column its respective values to be entered.

How can i achieve this use case in NiFi? What Regex I should use in the ReplaceText to achieve this. Anny help is appreciated. Thank You!

What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it? — Toto
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated? — Shrads
Sounds like a few lines of PHP code. (I never heard of Nifi.) — Rick James

mattyb mattyb · Accepted Answer · 2019-01-03T21:53:42

You can use GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "Address": "[#2].Address",
        "Lastname": "[#2].Lastname",
        "*Name": {
          "$": "[#3].Name",
          "@": "[#3].Value"
        }
      }
    }
  }
]

Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.

perform Regex on JSON data across each row and push into the MySQL table using NiFi

1 Answers