nifi - Writing/Inserting entire flowfile content

[#1]

I have JSON input. I need to insert a database table column with the entire content of that JSON.

Flow is as follows,

Read a JSON from kafka then insert a record in a database. column A with one particular value of the input JSON and Column B with entire JSON.

Thanks in advance,

Shamji

[#2 · Accepted solution]

You can use EvaluateJsonPath to extract the one particular value of the input JSON into an attribute, then you can use ReplaceText to create a SQL statement (using NiFi Expression Language and group referencing, see ReplaceText documentation for more details) that refers to both the extracted attribute and the incoming flow file content. Then you can send that to PutSQL to insert into the database.

For example, if you have the following JSON:

{
  "id": 100,
  "user" : {
    "name": "Joe Smith",
    "email": "jsmith@mycompany.com",
    "age": 42
  }
}

And you have a database table called "myTable" with two columns, "id" and "json". Then you could use EvaluateJsonPath to set an attribute "json.id" with a JSONPath expression of "$.id". The the ReplaceText could replace the whole content with something like the following:

INSERT INTO myTable VALUES (${json.id}, '$1')

I haven't tried this to see if it works as-is, but that is the basic approach. You may have to do something with escaping the JSON or quote characters or something like that.

[#3]

Thanks for the response.

I tried that. $1 is not getting replaced with JSON. I was using the same processors for this purpose.

[#4]

It worked. Thanks a lot..!! I was using 'ALWAYS REPLACE' instead of 'Regex Replace'