Using the result of NIFI ConvertAvroToJson in a where Clause of ExecuteSQL

[#1]

Hi, I do want to use the result of an AvroToJSON as Part of a where clause in ExecuteSQL. I do get a result from Avro2JSON:

[ {
"PKEY" : "20222160749",
"BOID" : "20082919283",
"TITLE" : "Rechnung eingehend",
"ITSPARTNER" : "M11538622",
"ITSKOMMPARTNER" : "M11538622",
"JOURBOREPRESENT" : ", , ,",
"SACHVERHALT" : null,
"BEMERKUNG" : null
} ]

The JSON PATH Expression

$..ITSPARTNER extracts the correct value.

I've tried to do something like this in the Query of ExecuteSQL:

Select * from syr_src_adm.partner where STATEEND = TO_DATE('01.01.3000', 'dd.mm.yyyy') and REPLACED = TO_DATE('01.01.3000', 'dd.mm.yyyy') and BOID = '$[*]..ITSPARTNER'

But this seams not to work. Is the Syntax correct?

[#2]

You'll need to extract the JSON path for ITSPARTNER out into an attribute using EvaluateJsonPath, then you can refer to the attribute in the query for ExecuteSQL. So for example in EvaluateJsonPath you can add a user-defined property (using the + button on the top-right of the Properties tab) to set an attribute with key "itspartner" to "$..ITSPARTNER", then in ExecuteSQL, your query would be the following:

Select * from syr_src_adm.partner where STATEEND = TO_DATE('01.01.3000', 'dd.mm.yyyy') and REPLACED = TO_DATE('01.01.3000', 'dd.mm.yyyy') and BOID = '${itspartner}'

[#3]

Hi Matt,

it worked so far, but how do I get rid of the [ ] in the result of the EvaluateJsonPath? The generated query now looks like ....

TO_DATE('01.01.3000','dd.mm.yyyy')and BOID =["M12121212"]