question Re: nifi - Writing/Inserting entire flowfile content in Archives of Support Questions (Read Only)

nifi - Writing/Inserting entire flowfile content

shamjithnair — Thu, 20 Apr 2017 10:42:42 GMT

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

Re: nifi - Writing/Inserting entire flowfile content

mburgess — Thu, 20 Apr 2017 20:13:01 GMT

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.

Re: nifi - Writing/Inserting entire flowfile content

shamjithnair — Thu, 20 Apr 2017 23:25:30 GMT

Thanks for the response.

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

Re: nifi - Writing/Inserting entire flowfile content

shamjithnair — Fri, 21 Apr 2017 05:35:13 GMT

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