Created 07-14-2021 03:33 AM
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:
Created 07-28-2022 04:50 PM
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.apach...
Created 07-31-2022 06:09 PM
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-...
Cheers,
André
Created 07-31-2022 06:09 PM
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-...
Cheers,
André