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

[flash1293]

@nik9000 It's an edge case, but can you clarify the behavior of INSIST_🐔 if there are multiple values for a field due to flattening?

{
  "resource": {
    "attributes": {
      "host.name": "abc"
  },
  "resource.attributes.host.name": "def"
}

FROM * | INSIST_🐔 resource.attributes.host.name // will resource.attributes.host.name ["abc", "def"] or "abc" or "def"?

[nik9000]

Resolves as a multivalued field with ["abc", "def"]. At least, that's how it should work and that's how our usual _source loading works.

…

On Sun, May 11, 2025 at 7:46 AM Joe Reuter ***@***.***> wrote: *flash1293* left a comment (elastic/elasticsearch#115092) <#115092 (comment)> @nik9000 <https://github.com/nik9000> It's an edge case, but can you clarify the behavior of INSIST_🐔 if there are multiple values for a field due to flattening? { "resource": { "attributes": { "host.name": "abc" }, "resource.attributes.host.name": "def" } FROM * | INSIST_🐔 resource.attributes.host.name // will resource.attributes.host.name ["abc", "def"] or "abc" or "def"? — Reply to this email directly, view it on GitHub <#115092 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABUXIQLFFDICA5QMAK6QG32542CJAVCNFSM6AAAAABQFXM4YGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDQNRZG44DGMRZGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>