Support Questions

Find answers, ask questions, and share your expertise
Announcements
Welcome to the upgraded Community! Read this blog to see What’s New!

QueryRecord processor issue with nested JSON

avatar
Explorer

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.

1 ACCEPTED SOLUTION

avatar
Master Collaborator

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.

--
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.

View solution in original post

2 REPLIES 2

avatar
Master Collaborator

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.

--
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.

avatar
Explorer

Thank you Araujo. This has helped a lot. 

Labels