Query list field CONTAINS without unnest

Hey! I am wondering if Dremio supports some type of CONTAINS SQL operator to query a field that is a list for a certain item.

“field1”: [
“field2”: “value2”,
“field3”: {
“nestedfield1”: “nestedvalue1”,
“nestedfield2”: “nestedvalue2”
“field4”: “value4”

I would like to do a query for:

select * from table
where field1 CONTAINS ‘listvalue2’

Can this be done without unnest/flatten? Is there any query to reflect what I am asking? I ask this because the actual dataset I want to work with is very large with many list items like this. If I had to unnest/flatten, it would exponentially increase the amount of records I would have.


If it is an arrat/list type, you have to currently unest/flatten