I am trying to filter the record in Kafka Lenses base on the value of an object nested in array. I want to find all records with engineer "id" : "555" in an array. This are two sample records I want to get where the index of the engineer with "id" : "555" could be different in each record
Record A
{
"payload": {
"Data": {
"project": "A"
},
"engineer": [
{
"name": "Jerry",
"id": "111",
"join_date":"12-10-2020"
},
{
"name": "Fanny",
"id": "555",
"join_date":"11-23-2020"
}
]
}
}
Record B
{
"payload": {
"Data": {
"project": "B"
},
"engineer": [
{
"name":"Fanny",
"id": "555",
"join_date":"10-18-2021"
},
{
"name":"Terry",
"id": "999",
"join_date":"10-26-2020"
}
]
}
}
Obvious, I am not able to do something like ARRAY_CONTAINS or
SELECT *
FROM topic_name
WHERE payload.engineer[*].id = "555"
LIMIT 10