ESQL: Allow reading fields from source dynamically

[flash1293]

Description

Currently, it's not possible to directly read fields which are only available in source, but not mapped:

PUT my-restricted-index
{
  "mappings": {
    "dynamic": false,
    "properties": {
      "a": {
        "type": "keyword"
      }
    }
  }
}

POST my-restricted-index/_doc
{
  "a":  "fsfd",
  "b": "xyz"
}

// fails with "Unknown column"
POST _query
{
  "query": "FROM my-restricted-index | WHERE b == \"xyz\""
}

However, it's possible to make this work using runtime fields:

PUT my-restricted-index/_mapping
{
    "runtime": {
      "b": {
        "type": "keyword"
      }
    }
}


// works now
POST _query
{
  "query": "FROM my-restricted-index | WHERE b == \"xyz\""
}

To allow users to both control their storage cost by not mapping and indexing rarely used fields and still being able to comfortably querying them via ESQL, there should be a way to instruct ESQL to read from source without having to deal with runtime fields. A possible syntax could look like this:

// works even without runtime field mapping
POST _query
{
  "query": "FROM my-restricted-index | EVAL b = FROM_SOURCE(\"b\", \"keyword\") | WHERE b == \"xyz\""
}

Ideally, there is a way for FROM_SOURCE to transparently leverage indexed fields if they exist - in this case the example above would check whether the field b is mapped - if not, it will retrieve it from source like the runtime field. If it's mapped as a keyword already, FROM_SOURCE becomes a no-op.

[elasticsearchmachine]

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

[nik9000]

Ideally, there is a way for FROM_SOURCE to transparently leverage indexed fields if they exist - in this case the example above would check whether the field b is mapped - if not, it will retrieve it from source like the runtime field. If it's mapped as a keyword already, FROM_SOURCE becomes a no-op.

That sounds pretty possible.

[jbaiera]

Throwing a +1 on this issue. The new failure store documents do not provide a mapping for their document source so that they can reliably index a failed document in the event of a mapping explosion or type conflict. Having the ability to supply runtime field support would greatly improve the user experience for searching this kind of data with ES|QL.

[nik9000]

There's a prototype available currently going under the name INSIST_🐔 which loads keyword fields. The goal is:

1. If the field is already mapped at the type you asked for (currently only has keyword) then this is a noop
2. If the field is mapped as another type then this is a cast
3. If the field is not mapped then it's loaded from _source as that field type