How to search nested documents in elastic using Trino connector

[gilbob20000]

I'm new to trino and I'm trying to use it to query nested objects in elastic search.

This is my mapping in elasticsearch:

{
"product_index": {
"mappings": {
"properties" :{
"id" : { "type" : "keyword"},
"name" { "type" : "keyword"},
"linked_products" :{
"type": "nested",
"properties" :{
"id" : { "type" : "keyword"}
}
}
}
}
}
}

We are trying to query the data according to the id field.
This is the query in Elastic:

get product_index/_search
{ 
"query": {
"nested" : {
"path" : "linked_products",
"query": {
"bool": {
"should" : [
{ "match" : {"linked_products.id" :123}}
]
}
}
}
}
}

what is the syntax I need to use to query the id field from trino using the elastic connector?

We tried to query the id field in 2 ways:

1. Trino query -
   I added the isArray indicating to the mapping in elasticserach.
   When we try to query according to the id field the Pushdown to elastic doesn't
   happen and the connector retrieve all the documents to trino (this only happens with queries on nested documents).

2. send es-query from trino to elastic:
   SELECT * FROM es.default."$query:"
   It works but when we are trying to retrieve id's with many documents we got timeout from the elastic client.
   I don't understand from the documentation if it is possible to perform scrolling when are using es-query to avoid the timeout problem.

Can anyone help?

[bitsondatadev]

Hi @gilbob20000 I answered your question on Stack Overflow and added comments to address your new questions.

https://stackoverflow.com/questions/67667313/trino-presto-with-elastic-how-to-search-nested-objects/67843697#67843697

Please find the answer there and accept it if that answeres the question. Thanks!