Is there a way to loop through unknown values(in flow files) so that I can use them one by one to execute new queries?

[#1]

Here is my problem. I have multiple rows coming out of a table and I have to read each row one by one and execute a SQL query using values from each of the rows. For e.g. I have 10 ids and their related fields, I have to read each row once, execute an SQL query using those values, then move to the next id and its related fields and execute the SQL again with the 2nd id details and so on..

Is there a looping mechanism in Nifi I can use? I have seen examples of EvaluateJSONPath and then using RouteOnAttribute, however, as my values are unknowns I can't use RouteOnAttribute. I was thinking of writing them to a file and then read them one by one using a loop written in execute script. Is that the only choice?

Appreciate your help!

@Matt Burgess @Bryan Bende

[#2 · Accepted solution]

How is your data coming into NiFi? If it is a single flow file with all the rows (such as ExecuteSQL which returns an Avro file with records in it), then you can use SplitAvro and then downstream each flow file can be processed separately, with no looping required. If your input is a text file you can use SplitText, if JSON then SplitJSON, etc.

If instead you have a number (say 10), and you need to fetch rows with ids 1-10, you can either use ExecuteSQL and get all rows < 10. If I am misunderstanding your use case and you do need to loop, then after you get your loop variable into an attribute (perhaps with EvaluateJSONPath as you mention), then you can use RouteOnAttribute only to see if it is time to exit the loop (${loopVariable:gt(0)} for example). Otherwise you can use UpdateAttribute to increment or decrement the counter, and send that output back to the beginning of the loop.

[#3]

Thanks for the reply and help Matt, I was actually looking for a way to go through the output of a query one by one, one record at a time and I think was not able to explain properly in my question. I thought there is no way to do it in Nifi as the output of ExecuteSQL is a continuous stream and I might have to device a way to loop through the output, but then I found out about SplitAvro and used it with 1 record at a time after the ExecuteSQL processor and it did what I wanted :).

Thanks,

Samir