Kafka Lenses SQL - How to WHERE filter based on objects nested in an array

user_2958377 0
Viewed 562

I am working in Kafka Lenses v2.2.2. I need to filter based on a the value of an object inside an array.

Sample message (redacted for simplicity):

{
        "payload": {
            "Data": {
                "something" : "stuff"
             },

            "foo": {
                "bar": [
                    {
                        "id": "8177BE12-F69B-4A51-B12E-976D2AE37487",
                        "info": "more_data"
                    },
                    {
                        "id": "06A846C5-2138-4107-A5B0-A2FC21B9F32D",
                        "info": "more_data"
                    }
                ]
            }
    }

In lenses this actually appears as a nested object with a integer properties... 0, 1, etc.

So I've tried this, but it is throwing an error: .0 appears out of place

SELECT * 
FROM topic_name
WHERE payload.foo.bar.0.id = "8177BE12-F69B-4A51-B12E-976D2AE37487" 
LIMIT 10

I tried wrapping the 0 in double/single quotes as well and that throws a 500 error.

I copied and pasted the UUID from the first message in the topic, so it's definitely there. I also copy and pasted the labels to rule out typos. I am thinking there is some special way to access arrays with nested objects like this, but I'm struggling to find any documentation or videos discussing it.

I can be confident the value is stored in the first array element, but methods that can search all objects would be awesome as well.

apache-kafka sql
1 Answers

The syntax (if you know the array index - as in my initial question) is:

SELECT * 
FROM topic_name
WHERE payload.foo.bar[0].id = "8177BE12-F69B-4A51-B12E-976D2AE37487" 
LIMIT 10

Though I am still struggling to do this if the array index is unknown and you need to check them all. I'm assuming at this point it's not possible without a series of OR statements in the WHERE clause that checks them all.

user_29583770
Related