QueryRecord processor issue with nested JSON

[#1]

Hi,

I am facing issue accessing one of the child json attribute while forming SQL in QureyRecord processor. PFB details

here is my Avro schema added for JSON reader and writter.

{
"name": "MyClass",
"type": "record",
"namespace": "com.acme.avro",
"fields": [
{
"name": "labels",
"type": {
"name": "labels",
"type": "record",
"fields": [
{
"name": "__name__",
"type": "string"
},
{
"name": "cucsMemoryUnitInstanceId",
"type": "string"
},
{
"name": "instance",
"type": "string"
},
{
"name": "job",
"type": "string"
},
{
"name": "monitor",
"type": "string"
},
{
"name": "site_identifier",
"type": "string"
}
]
}
},
{
"name": "name",
"type": "string"
},
{
"name": "timestamp",
"type": "string"
},
{
"name": "value",
"type": "string"
},
{
"name": "producedAt",
"type": "string"
}
]
}

now the SQL quary for first level attribute works fine. 

like SELECT * from FLOWFILE where name = 'xyz'   or 

SELECT * from FLOWFILE where producedAt = 'dd-mm-yyyy'

however If I have to access attribute of nested JSON it wont work  

e.g

SELECT * from FLOWFILE where site_identifier = 'xyz'    --> This throws the error.

[#2 · Accepted solution]

Hi, @Onkar_Gagre ,

You can use a Record Path expression to do what you want in a QueryRecord processor. Please try the example below:

SELECT *
FROM FLOWFILE
WHERE RPATH(labels, '/site_identifier') = 'xyz'

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

[#3]

Thank you Araujo. This has helped a lot.