Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Highlighted

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

New Contributor

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 REPLIES 2
Highlighted

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

Super Guru

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}'
Highlighted

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

New Contributor

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"]

Don't have an account?
Coming from Hortonworks? Activate your account here