Taming PUNKs: How ES|QL queries Elasticsearch fields it was never told about

Get hands-on with Elasticsearch: Dive into our sample notebooks in the Elasticsearch Labs repo, start a free cloud trial, or try Elastic on your local machine now.

How do you make an analytical query engine use data that it cannot know exists? You “just” read the query, since everything that the user asks for is right there. Right?

In Elasticsearch 9.5, Elasticsearch Query Language (ES|QL) queries no longer fail when a field isn't in the mapping. The new unmapped_fields setting lets queries load values from _source or fill with nulls, so queries keep working even when a backing index changes and a field goes missing, and can use unmapped data without reindexing. Here’s how we built that: the design choices and the edge cases (including a class of fields we nicknamed PUNKs), along with the testing strategies that gave us the confidence to ship it in general availability (GA).

Why ES|QL queries fail when a field is unmapped

You built a visualization using an ES|QL query. You refined it, and the query grew. You’re at 15 chained commands and counting, but it does just the right thing. It works, and your dashboard is useful.

Your query uses an index from a remote cluster, say my-remote:logs-foo. But actually, logs-foo is an alias, and at some point, the remote cluster makes it point to a different backing index. The new index is missing a field that’s used in your query, and your query and visualization break.

Or maybe you have an already fairly large index, and while building ES|QL queries on top of it, you realize that you’d like to use a field in the indexed documents that unfortunately never made it into the index mapping. You could reindex the data, but that would take hours.

ES|QL’s unmapped_fields setting is meant to deal with these types of situations.

If your query looks like this:

FROM index | EVAL uppercased = TO_UPPER(some_field)

and some_field is unmapped, ES|QL’s default behavior is to fail with a verification exception.

You can use the unmapped_fields setting to instead either fill some_field with nulls or read it from the document’s _source, like so:

// Fill some_field with nulls
SET unmapped_fields="NULLIFY";
FROM index | EVAL uppercased = TO_UPPER(some_field)

// Read data from _source
SET unmapped_fields="LOAD";
FROM index | EVAL uppercased = TO_UPPER(some_field)

How ES|QL resolves queries with field caps

Before we jump into the inner workings of unmapped_fields, we have to look into how ES|QL resolves queries regularly. Let’s consider the above query:

FROM index | EVAL uppercased = TO_UPPER(some_field)

We said that if some_field isn’t in the mapping for index, ES|QL will reject the query. How does it make that decision?

How field caps tells ES|QL which fields exist

In a typical schema-on-write fashion, Elasticsearch clusters maintain mappings with their respective indices. As a first step, ES|QL makes an internal request to the field caps endpoint to determine which fields the index has. It then passes the query, together with the field caps response, to the query planner, which consists essentially of the query analyzer (unrelated to analyzers of text fields) and query optimizer. The analyzer makes sense of raw names, like some_field, and notices that they correspond to index fields (or not). If all went well, the query is then passed on to the optimizer, which rewrites the query for efficiency, before it’s handed to the compute engine for execution.

How the analyzer resolves field names in the query plan

Let’s zoom in to the analyzer. The parsed query is represented in a tree structure, and the analyzer partially rewrites it, one command at a time, until it either has resolved all references or not.

For illustration, let’s use a somewhat more complex query and see how the analyzer would resolve it:

FROM index
| EVAL uppercased_mapped = TO_UPPER(mapped_field)
| EVAL uppercased_unmapped = TO_UPPER(unmapped_field)

The parsed tree is actually a chain here, and it looks something like this:

Eval[TOUPPER(?unmapped_field) AS uppercased_unmapped]
\_Eval[TOUPPER(?mapped_field) AS uppercased_mapped]
  \_From[mapped_field{f}]

The analyzer then moves up through the query tree to try and resolve the field names used in every command.

This is a simplified version of how we represent parse trees in tests and when debugging. The bottom of the chain corresponds to the FROM command and contains a list of all mapped fields that we know about, obtained from the field caps endpoint. (The {f} suffix marks an actually mapped field for better distinction later.)

The two EVAL nodes on top of it correspond to the remaining commands, and their fields are still unresolved, expressed by the question mark ? in front of the name. At this point, the analyzer still has to check whether they correspond to existing index fields.

For the EVAL that defines uppercased_mapped, it can see that the previous command outputs mapped_field, so the unresolved ?mapped_field marker can be replaced by a real field reference:

Eval[TOUPPER(?unmapped_field) AS uppercased_unmapped]
\_Eval[TOUPPER(mapped_field{f}) AS uppercased_mapped] // mapped_field: resolved!
  \_From[mapped_field{f}]

Next, it encounters the topmost EVAL, which defines uppercased_unmapped. The previous tree nodes produce only two fields: [mapped_field, uppercased_mapped]. The reference ?unmapped_field thus has to remain unresolved. We bail here and emit the verification exception to the user.

How unmapped_fields LOAD and NULLIFY work

Adding unmapped fields to the query plan

When using unmapped_fields=”NULLIFY” or ”LOAD”, we do something else; we act as if the field was actually in the index. The analyzer adds unmapped_field to the From node and marks it as unmapped to signal to the compute engine that this has to be read from _source or filled with nulls. Let’s express this with a {u} (for unmapped):

Eval[TOUPPER(?unmapped_field) AS uppercased_unmapped]
\_Eval[TOUPPER(mapped_field{f}) AS uppercased_mapped]
  \_From[mapped_field{f}, unmapped_field{u}]  // add unmapped_field

After amending the From, the analyzer can continue trying to resolve the topmost Eval node. It sees that the upstream nodes produce the fields [mapped_field, unmapped_field, uppercased_mapped] and thus unmapped_field can be correctly resolved:

Eval[TOUPPER(unmapped_field{u}) AS uppercased_unmapped] // resolved!
\_Eval[TOUPPER(mapped_field{f}) AS uppercased_mapped]
  \_From[mapped_field{f}, unmapped_field{u}]

The query plan is now fully resolved and can be passed down the regular optimization-execution pipeline. Other than the actual value extraction mechanism, everything stays the same. Schematically, the workflow looks like this:

Example: enabling unmapped fields with the SET directive

To give an example, let’s fire up a cluster and create an index with non-dynamic mappings.

PUT /index
{                                 
  "mappings": {
    "dynamic": false,
    "properties": {
      "mapped_field": {"type": "keyword"}
    }
  }
}

POST /index/_doc?refresh
{
  "mapped_field":"foo"
  "unmapped_field": "bar"
}

We can run the example query, above:

POST /_query
{
  "query": """
           FROM index
           | EVAL uppercased_mapped = TO_UPPER(mapped_field)
           | EVAL uppercased_unmapped = TO_UPPER(unmapped_field)
           """
}

This should result in the error message:

Unknown column [unmapped_field], did you mean [mapped_field]?

To make things work, we can prepend SET unmapped_fields=”...”; with LOAD or NULLIFY:

POST /_query
{
  "query": """
           SET unmapped_fields="LOAD";
           FROM index
           | EVAL uppercased_mapped = TO_UPPER(mapped_field)
           | EVAL uppercased_unmapped = TO_UPPER(unmapped_field)
           """
}

 mapped_field  |unmapped_field |uppercased_mapped|uppercased_unmapped
---------------+---------------+-----------------+-------------------
foo            |bar            |FOO              |BAR

Inspecting the analyzer's rewrite steps

If you want to see what the query analyzer is doing to the parse tree, you can log the query rewrite steps, like so:

PUT /_cluster/settings"
{
  "transient" : {
    "logger.org.elasticsearch.xpack.esql.analysis.Analyzer.changes": "TRACE"
  }
}

This will log a line containing Rule rules.ResolveUnmapped applied with change… You’ll see that unmapped_field is added to the bottom of the parse tree as described above.

Why we have to infer the schema

Of course, this isn’t the only possible method to deal with unmapped fields. Here are some alternatives:

1. We could also scan or probe the documents in index to determine that their _source actually has the unmapped_field.

2. We could disable verifications in the analyzer and make the compute engine blindly pass unmapped fields through individual computation steps.

The first alternative front-loads more work to understand the actual schema of an index and thus generally increases latency. It doesn’t scale to large, highly distributed datasets. The second alternative isn’t viable since it means a large-scale change to how ES|QL’s compute engine is built, because it passes around streams of data with fixed columns from one operator to another.

In contrast, the approach we chose is neatly compatible with ES|QL’s existing optimization pipeline.

The trade-off is that the analyzer has to correctly infer a schema based on the actual index mappings (obtained from the field caps endpoint) and additional fields used inside the query. 

This isn’t always straightforward. There were two main challenges:

1. There are many different query shapes and commands that can be used. The mechanism needs to detect unmapped fields, update the proper FROM command, and pass the new field through the halfway resolved plan correctly in all cases.

2. There are many different mappings we have to deal with, and we specifically need to make our feature work correctly when mappings change over time on top of that.

In the following, we’ll focus on LOAD, although some problems (generally many fewer) also apply to NULLIFY.

Which index to load unmapped fields from for LOOKUP JOIN and FORK

To briefly illustrate the first problem, here are some choices we needed to make:

• Which index do we load from when using lookup joins? This one?

  FROM index
  | LOOKUP JOIN lookup-index ON match_field
  | EVAL uppercased_unmapped = TO_UPPER(unmapped_field)

  The unmapped_field cannot be attributed to both indices. We chose index since this is where we expect mappings to change more often than in lookup indices.

• Similarly, how do we deal with subqueries and views or the FORK command? In the following:

  FROM index
  | FORK (EVAL uppercased_unmapped = TO_UPPER(unmapped_field))
         (WHERE true)

  one fork branch triggers loading of an unmapped field. Is it also present in the other fork branch? (Yes, it should be, but it’s not obvious and is specifically not true if the two FORKs are replaced by independent subqueries.)

Two principles to keep queries working

The second problem, diversity of mappings and their evolution over time, is a far bigger driver for complexity. We strove for two basic usability principles:

• Queries that work in the default mode should generally still work when using unmapped_fields=”NULLIFY” and ”LOAD”.

• Queries that work when all fields are mapped should generally still work with NULLIFY and LOAD when a field becomes unmapped and vice versa.

The type of unmapped fields and inadvertent type conflicts

Let’s talk about data types to see where this leads to complexity. First, when using unmapped_fields=”LOAD”, we need to assume a data type for unmapped fields. We chose KEYWORD, which allows us to avoid type conflicts when reading from _source. One document can contain ”unmapped_field”: “foo”, and another can contain ”unmapped_field”: 123.4. It’s fine because we treat both as strings.

However, this is a violation of the second principle when a non-KEYWORD field happens to go unmapped. Consider this query:

SET unmapped_fields="LOAD";
FROM index | WHERE some_field > 10

If some_field becomes unmapped, we’ll have to assume that the KEYWORD type and the query will fail with a type conflict.

Type conflicts aren’t new and can be dealt with by using explicit casts in the query, like so:

SET unmapped_fields="LOAD";
FROM index | WHERE some_field::integer > 10

It would be great if ES|QL just inferred a useful type to cast to, but this is something for the future.

Type conflicts with partially unmapped fields, or: making PUNKs well behaved

In addition to fully unmapped fields, partially unmapped fields are everywhere and should also work with LOAD. Let’s look at a query that uses multiple indices.

Let’s say that there are indices index and index_without_some_field, containing just the following documents.

// index1
{
  "some_field": "foo"
}

// index2
{
  "some_field": "bar"
}

Now let’s consider the query:

FROM index, index_without_some_field

and assume that some_field is unmapped in index_without_some_field. This will return:

some_field
-------------
 foo
 null

because ES|QL doesn’t load unmapped fields per default.

Of course, when setting unmapped_fields=”LOAD”, we want to load from _source for index_without_some_field:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field

 some_field
-------------
 foo
 bar           // loaded from _source

As with fully unmapped fields, the case is simple when some_field is mapped as KEYWORD in index. When loading from _source for index_without_some_field,  we treat the field as KEYWORD as well, so there’s no conflict.

What makes a field a PUNK

The case is less clear when some_fieldis partially unmapped and the mapped leg is of a type other than KEYWORD. Such fields caused a lot of trouble until we found the best solution, which makes their acronym quite fitting: partially unmapped non-keyword fields, or PUNKs.

Unfortunately, PUNKs are far from being esoteric. For instance, it’s very natural to filter on a PUNK:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field | WHERE some_field > 10

If some_field is mapped as INTEGER in index, the type conflict looks like this:

• Mapped as an INTEGER in index.

• Unmapped in index_without_some_field and thus treated as KEYWORD.

This can again be resolved manually by providing an explicit cast:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field | WHERE some_field::integer > 10

But this is far from acceptable. Even queries that work fine without NULLIFY and LOAD typically have some PUNKs; the unmapped leg is simply treated as null then. Both guiding principles are violated if LOAD requires an explicit cast here.

Casting implicitly to the mapped type

The solution is to introduce an implicit cast to the mapped type. In this case, we know that some_field is an INTEGER in index, and thus we treat it essentially as if the user wrote:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field
| EVAL some_field = some_field::integer
| WHERE some_field > 10

This means that queries that work without LOAD keep working. (ES|QL may even give you more data because we load the unmapped leg of PUNKs from _source.) Queries that used to work when a field is fully mapped also keep working when it goes unmapped in some (but not all) of its indices without having to alter the query in any way.

Don't throw it all away: Keeping ES|QL query optimization with unmapped fields

There's one more thing to get right; that is, to make sure that optimizations still work correctly with LOAD. Consider the previous query:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field | WHERE some_field > 10

ES|QL’s optimizer aggressively pushes down such WHERE filters and turns them into Lucene queries, so the compute engine doesn’t perform unnecessary work.

For this query, evaluating the filter in the compute engine would require fetching each and every document from the index; meaning, a full scan, very slow. If some_field was mapped as an INTEGER in both indices, we would instead perform a Lucene query, which looks like this:

{
  "range": {
    "some_field": {
      "gt" : 10,
      "boost" : 0.0
    }
  }
}

The compute engine then doesn’t have to load each document separately and check whether it matches the filter. Documents with some_field <= 10 are never fetched from the Lucene index, which is very efficient at this kind of filtering. Nice.

Why filter pushdown is unsafe for unmapped fields

If some_field is unmapped in index_without_some_field, however, it’s wrong to narrow the documents down using the same Lucene query, as Lucene interprets an unmapped some_field as null and thus no documents from index_without_some_field will ever match. This edge case is easy to miss, and it doesn’t help that there are several flavors of similar pushdowns. For instance, in the query:

FROM index, index_without_some_field | STATS COUNT(some_field)

the compute engine pushes even the counting to Lucene. Again, this is only correct if some_field is fully mapped.

This means that such optimizations can't apply to unmapped fields. It would be disappointing if a query used hundreds of indices and only one of them happened to not map some_field, causing the whole query to run unoptimized.

How the local optimizer recovers the fast path

Luckily, this problem has a solution, too. ES|QL actually has multiple optimizer runs:

1. First, a preliminary optimizer run on the node handling the _query request.

2. Then, a second, local optimizer run on every node we fan out to because we need to fetch documents from its shards.

The workflow after the initial optimization looks more like this:

If the current node happens to map some_field in all shards, the local optimizer detects this situation and treats some_field like any other fully mapped field, including performing Lucene queries to greatly narrow down the dataset to be processed. In fact, data nodes process LIMIT queries like:

SET unmapped_fields="LOAD";
FROM index, index_without_some_field
| WHERE some_field > 10
| LIMIT 1000

in batches of shards (to avoid loading too much data too eagerly), which includes a full local optimizer run per batch. This makes it even more likely to encounter batches where some_field is fully mapped, allowing ES|QL to run a fast Lucene query.

Is it working now? Testing unmapped_fields across every ES|QL query shape

As we have seen from the optimizer issues above, problems can hide in plain sight, even for very simple queries. Because unmapped_fields=”LOAD” can affect each and every kind of query, the surface area for bugs is essentially all of ES|QL.

Accordingly, getting good test coverage was tricky and challenged us to refine our testing strategies.

Reusing spec tests with unmapped_fields

Conveniently, ES|QL has an extensive corpus of test queries, together with expected result sets; we call them spec tests because they’re written using a simple text specification language, which looks roughly like this:

simpleEval
row a = 1 | eval b = 2
;

a:integer | b:integer
1         | 2
;

This lets us create new tests out of the existing ones by introducing slight variations. For instance, any existing test that runs without SET unmapped_fields=”...” should produce the exact same results when run with SET unmapped_fields=”NULLIFY”.

It also helped find major issues early in the development process, especially for NULLIFY. The LOAD setting changes the meaning of queries much more dramatically, limiting the usefulness of this approach. However, ES|QL also uses what we call generative testing; that is, we string together random commands, run the query, and then check whether the server reports a bug. This approach cannot confirm the correctness of results, but it still helped greatly with finding query types that didn’t work properly and resulted in some kind of error. (Property-based tests would be a refinement in the future by running the queries against a reference implementation. This way, correctness of results can also be checked.)

Testing type conflicts across different mappings

In the end, one of the most important testing dimensions was using different indices with various mappings in the same query. (Recall how, above, we had to deal with type conflicts to come up with a solid approach for PUNKs? It doesn’t end there; all kinds of type conflicts are more complex with LOAD.) Since we couldn’t automatically generate correct expected results, ES|QL’s test suite had to grow by adding more than 10,000 lines of CSV spec tests. Fortunately, adding such tests is a well-suited task for an AI agent, which has cut down the effort dramatically. (Of course, the test results were still reviewed by humans.)

All testing strategies together provided us with good confidence for the GA release of unmapped_fields with Elasticsearch 9.5.