SQL: improve the nested documents handling

[astefan]

At the moment, when a query needs to touch nested documents, it is creating a nested query for each where clause. Something like the following (simplified) for the select groupName from my_index where user.first = 'Alice' and user.last = 'Smith' query (where user is a nested field):

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "query": {
              "term": {
                "user.first.keyword": {
                  "value": "Alice"
                }
              }
            },
            "path": "user"
          }
        },
        {
          "nested": {
            "query": {
              "term": {
                "user.last.keyword": {
                  "value": "Smith"
                }
              }
            },
            "path": "user"
          }
        }
      ]
    }
  }
}

Which is a perfectly valid query for a nested documents scenario: get all documents where at least one of the nested values in it (user.first in this case) is Alice and also another nested value (not necessarily the first one) in it (user.last in this case) is Smith.
But there is also the scenario where one would want to get the same nested document to have those values, meaning an user named Alice Smith.

In this second scenario, the query that needs to be generated is different:

{
  "query": {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.first": "Alice" }},
            { "match": { "user.last":  "Smith" }} 
          ]
        }
      }
    }
  }
}

Source: https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180

To summarize this issue, I believe that the user creating the SQL query needs to be the decider between one functionality and the other. And there are two main (simplified) functionalities when it comes to nested documents:

1. what already exists in ES SQL at the moment, where for each condition in WHERE, ES SQL builds a separate nested query and combines them in a bool
2. what is described above where a single nested query should surround a bool query containing the WHERE conditions queries

From what I've seen in the past, I believe the most common functionality is the second one, where one is interested in the conditions applied to a nested document to be fulfilled at the same time. Also, I don't think ES SQL can decide by itself what kind of query to build, since this is a matter of functionality choice (something that only the user can decide upon).

[elasticmachine]

Pinging @elastic/es-search-aggs

[astefan]

Relevant discussion - #33204 (comment).

[paulcarey]

Hi, I'm just pinging to see if there's been any progress on this issue (or #33204) ? Thanks.

[astefan]

Hi @paulcarey,
Unfortunately, no. And there are no near future plans for it, either.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

superceded by ES|QL