Can NiFi string manipulate functions be used in QueryRecord?

user_6810218 0
Viewed 140

Can one use substring or concat functions in queryRecord SQL statement field? See I have flowfile { "field1": "1, Tom Johnson", "field2":"3", "field3":"xyz" } In QueryRecord processor SQL query is,

select substringAfter(/field1, ',') as NAME, substringBefore(/field2, ',') as ID, field3 from flowfile

, I got error when run the processor with error about query. Don't know what problem is. How can this be done?

The upstream is above flowfile and tried sql query as both below:

  select substringAfter(/field1, ',') as NAME, substringBefore(/field2, ',') as ID, field3 from flowfile

and

 select substringAfter(field1, ',') as NAME, substringBefore(field2, ',') as ID, field3 from flowfile

The query with PATH as /field1 are not accepted by processor. The second one trigger run time error during prepare the SQL statement. So are these NiFi function can be used in QueryRecord?

apache-nifi function string
1 Answers

You can use the UpdateRecord processor to do this.

enter image description here

You'll need to clean up the leftover fields afterwards, but that can be done with QueryRecord with:

SELECT NAME, ID, field3 from FLOWFILE

user_7002690
Related