question Re: QueryRecord processor issue with nested JSON in Support Questions

QueryRecord processor issue with nested JSON

Onkar_Gagre — Mon, 14 Mar 2022 08:01:42 GMT

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.

Re: QueryRecord processor issue with nested JSON

araujo — Tue, 15 Mar 2022 02:14:35 GMT

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.

Re: QueryRecord processor issue with nested JSON

Onkar_Gagre — Tue, 15 Mar 2022 06:19:39 GMT

Thank you Araujo. This has helped a lot.