Does array search support push down?

[Mvpanswer7]

I'm trying to query an array in ElasticSearch

data: "names":[{"name":"allen"},{"name":"bill"},{"name":"dave"},{"name":"poter"}]
goal: "select names from table where array_contains(names.name, "bill")"
but spark won't do predicate pushdown if SQL statement use array_contains function.
hint: names.name = ["allen","bill","dave","poter"]
I've tried

select * from table where array_contains(names.name,"bill")
-- and
select explode(names.name) as name from table as t1;select * from t1 where name = "bill"
-- and
select * from table where cast(names.name as string) like '%bill%'
All failed to do pushdown, any other ways to do it?

[jbaiera]

Unfortunately, the array_contains function is not included in the list of expressions that are translated and sent by Spark to the connector for pushdown. This would be a very easy filter to pushdown in the connector, but sadly we are not given a choice to do so. This link references the Spark code that translates catalyst expressions into the filter requests that data sources can use. We support almost all of the listed expressions at this time, but array_contains is not one of them.

[Mvpanswer7]

Thanks, is there a better way to query arrays in es?

[jbaiera]

When in doubt, you can always specify an elasticsearch query to your job by using the es.query property. I'll go ahead and close out this issue.

[Mvpanswer7]

why es.query will ignore params like "size" and "from"?

[jbaiera]

es.query is strictly for specifying the matching criteria for documents. size and from are ignored during the parsing. They also don't make much sense to use since ES-Hadoop uses the scroll API for all read operations.

[jbaiera]

Closing this as there's nothing actionable for project work here.

[Tagar]

with this change in Spark 3 it might be possible to push down array_contains now?
apache/spark#28366

possibly just add this elasticseach datasource to spark.sql.optimizer.nestedPredicatePushdown.supportedFileSources ?

[kant777]

@jbaiera