question Re: Apache NiFi : QueryRecord JSON Select child field in Support Questions

Apache NiFi : QueryRecord JSON Select child field

janis-ax — Wed, 14 Jul 2021 10:33:05 GMT

Is it possible to select with the QueryRecord processor also child fields of an given JSON structrue?

E.g. following JSON:

{ "name" : "Max", "id" : 1, "location" : { "city" : "Frankfurt" } }

And I would do the following query:

SELECT name, location.city FROM FLOWFILE

But this throws an error:

QueryRecord[id=a4744340-017a-1000-6944-73fe4d362d90] Unable to query StandardFlowFileRecord[uuid=706e8ae8-f76c-4ebb-9c65-c46cb7026c89,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1626251405764-2, container=default, section=2], offset=790026, length=67],offset=0,name=55b72cfd-5308-4e83-9304-166193e76478,size=67] due to java.sql.SQLException: Error while preparing statement [SELECT "name", "location.city" FROM FLOWFILE]: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Error while preparing statement [SELECT "name", "location.city" FROM FLOWFILE]

Is there something that I'm doing wrong? Here is the configuration:

Re: Apache NiFi : QueryRecord JSON Select child field

stevenmatison — Thu, 15 Jul 2021 14:57:01 GMT

Your sample query has no "quotes" but the one configured does. Just wanted to make sure you tried without those quotes ?

Re: Apache NiFi : QueryRecord JSON Select child field

janis-ax — Fri, 16 Jul 2021 07:55:41 GMT

I tried both ways. For keys on the root level, it doesn't matter if you use quotes. If I want to access child keys I got following error:

Re: Apache NiFi : QueryRecord JSON Select child field

stevenmatison — Fri, 16 Jul 2021 12:02:50 GMT

Understood, had to make sure! Next for good measure, make sure flow works for just name. This will show you if the issue is the entire setup or with just "location.city".

Next look at the configurations for the Reader/Writer and share those for us to see incase they are not default configs,etc. I believe the particular error:

SQLException: Error while preparing statement

occurs with a schema conflict or issue with flowfile being different than expected schema.

Re: Apache NiFi : QueryRecord JSON Select child field

janis-ax — Fri, 16 Jul 2021 13:03:11 GMT

For only "name", it's working fine, I get following FlowFile content:

I guess the issue is within the ControllerServices. I used JSON-TreeReader but with default configuration:

Re: Apache NiFi : QueryRecord JSON Select child field

mkohs — Thu, 28 Jul 2022 23:50:26 GMT

Hi @janis-ax ,

This reply comes a bit late but I was just researching the same issue. According to the official NiFi documentation, this should be possible using the "RPath" function in the 'Where' clause.

Check out the "SQL over hierarchical data" section here: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/latest/org.apache.nifi.processors.standard.QueryRecord/additionalDetails.html

Re: Apache NiFi : QueryRecord JSON Select child field

araujo — Mon, 01 Aug 2022 01:09:44 GMT

As @mkohs mentioned, using the RPATH function is the way to go here.

It can be used on the SELECT clause, for projection, as well as on the WHERE cause, for filtering.

You can see a similar use of it in this post here: https://community.cloudera.com/t5/Support-Questions/QueryRecord-processor-issue-with-nested-JSON/td-p/338556

Cheers,

André

Re: Apache NiFi : QueryRecord JSON Select child field

janis-ax — Tue, 02 Aug 2022 15:11:23 GMT

Thanks this works! I just have to use RPATH_STRING

Re: Apache NiFi : QueryRecord JSON Select child field

Kiranq — Tue, 06 Jun 2023 11:45:00 GMT

Hi @mkohs,

I am using QueryRecord processor and using SQL len function in the select statement. However, the processor results with the below error: "No match found for function signature len(CHARACTER)"

Is this the processor specific error because the same sql statement in running fine in sql server.
Any help would be appreciated.

Re: Apache NiFi : QueryRecord JSON Select child field

SAMSAL — Tue, 06 Jun 2023 13:24:11 GMT

Hi,

Nifi QueryRecord uses Apache Calcite SQL. Per the documentation (https://calcite.apache.org/docs/reference.html ) you can use either CHAR_LENGTH or CHARACTER_LENGTH to get the string size. Both functions seem to have the same behavior according to the documentation.

If you find this helpful please accept solution.

Thanks

Re: Apache NiFi : QueryRecord JSON Select child field

Kiranq — Wed, 07 Jun 2023 08:58:51 GMT

This worked. Thanks.