Support Questions
Find answers, ask questions, and share your expertise

Apache NiFi : QueryRecord JSON Select child field

Explorer

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:

janis-ax_0-1626258776077.png

 

4 REPLIES 4

Re: Apache NiFi : QueryRecord JSON Select child field

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

Explorer

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: 

 

janis-ax_0-1626422128111.png

 

Re: Apache NiFi : QueryRecord JSON Select child field

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

Explorer

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

janis-ax_0-1626440525178.png

 

 

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

janis-ax_1-1626440579211.png