NiFi SplitJSON and ExecuteSQL

Question

In a NiFi flow, I want to read a JSON structure, split it, use the payload to execute a SQL query, and finally output each result in a JSON file.

However I am having problems retrieving the value of the splitted FlowFile's attribute in the ExecuteSQL processor.

1. GenerateFlowFile processor, with a JSON structure as Custom Text

2. SplitJSON, with data as the attribute to be splitted

3. ExecuteSQL, with the query I am trying to execute, using the SplitJSON's payload attribute ${id}

At this point I am getting a log error of a SQL syntax error. Apparently the ExecuteQuery processor is not parsing the expression on its SQL select query property.

Is there any intermediate processing left to be done after splitting the JSON? What am I missing?

Answer 1

You need an additional EvaluateJsonPath (or ExtractText) processor between SplitJson and ExecuteSQL -- the Expression Language expression cannot evaluate the flowfile content for parameter substitution, so your SQL expression ends up being SELECT * FROM foo WHERE id = ; which is why you have a syntax error.

Expression Language reads from flowfile attributes, so you need to parse the JSON content into an accessible attribute. The EvaluateJsonPath processor does exactly this. All you need to do is add a custom property (click the + on the top right of the properties dialog) and extract the id value from the JSON into a flowfile attribute. Then connect the matched relationship from this processor to the ExecuteSQL processor.