ES|QL in Elasticsearch 9.1: LOOKUP JOIN for log enrichment

On 29 July 2025 Elastic released Elasticsearch 9.1 and 8.19, the last minor version of the 8.x line. Most of the attention went to vectors and AI, but for a team that works with logs and alerts every day the most important change is in ES|QL, the piped query language of Elasticsearch: the LOOKUP JOIN command is now GA (generally available). Until now it was a technical preview. From 9.1 and 8.19 you can use it in queries that are meant to run in production. Below we describe what it is good for, how to set it up and which limitations are worth knowing before the first use.

What LOOKUP JOIN does

The command adds columns from a second, small index (the lookup index) to your query results when the value in the chosen field is the same on both sides. It behaves like a left join in SQL: a row with no match stays in the results and the new columns get null. If several documents in the lookup index match, the results contain several rows.

In practice this means reference data no longer has to be copied into every document at write time. Typical uses:

• checking whether source addresses in firewall logs appear on a list of known malicious addresses,
• adding the owner and environment of a host from an inventory to your metrics,
• tagging application logs with the team responsible for the service, so that tickets reach the right people faster.

How to set it up

The lookup index must use the lookup index mode. Such indices always have a single shard, so they suit lists, not high-volume data. An example with a threat list:

PUT threat_list
{
  "settings": { "index.mode": "lookup" },
  "mappings": {
    "properties": {
      "source.ip":    { "type": "ip" },
      "threat_level": { "type": "keyword" },
      "threat_type":  { "type": "keyword" }
    }
  }
}

This query joins firewall logs with the list and keeps only the hits:

FROM firewall_logs
| LOOKUP JOIN threat_list ON source.ip
| WHERE threat_level IS NOT NULL
| SORT @timestamp
| KEEP @timestamp, source.ip, action, threat_type, threat_level
| LIMIT 100

Two practical notes. The order of results after LOOKUP JOIN is not guaranteed, so add SORT if you need one. Queries are also faster when fewer rows go into the join, so a WHERE filter on the source data belongs before LOOKUP JOIN. Elasticsearch tries to do this itself where it can.

LOOKUP JOIN or ENRICH

You can also enrich logs with the enrich processor at write time. The difference is that enrich needs a policy that has to be executed after every change to the data, while you update a lookup index with an ordinary write and the next query sees the change immediately. The Elastic documentation points to LOOKUP JOIN as the better choice when:

Situation	Choice
Reference data changes often	LOOKUP JOIN
You do not want to process data at write time	LOOKUP JOIN
You need to match on any field of the index	LOOKUP JOIN
You use document-level or field-level security	LOOKUP JOIN
You need to match on ranges or spatial relations	ENRICH

The last row shows that ENRICH does not go away. For matching IP address ranges or locations it remains the right tool.

Limitations in version 9.1

Before you build reports on it, check the limitations in the documentation for versions 9.0 and 9.1:

• the join is on a single field and on equality only, not on ranges,
• there must be one lookup index, with no wildcards (an alias or a specific name),
• the field name in ON must exist in the query results, so you sometimes need RENAME or EVAL,
• the query is stopped by a memory circuit breaker if too many documents match or they are very large, because the join works in batches of about 10,000 rows,
• in these versions the join does not work in cross-cluster queries, and both indices must be local.

The 9.1 improvements include several useful ones: joining on numeric fields of different but compatible types (for example long with integer), support for aliases and for date_nanos fields without losing precision.

What else changes in ES|QL after the upgrade

The 8.19 and 9.1 release also changes a default that matters to anyone with alerts or reports built on ES|QL.

The allow_partial_results setting is now on by default. If one shard is temporarily unavailable, the query returns results from the rest instead of failing. Likewise skip_unavailable now covers more errors on remote clusters. The response carries an is_partial flag, which you need to check wherever completeness matters, for example in reports for an auditor. Queries are also retried automatically after shard-level failures, which helps during node restarts in an upgrade.

Elastic also announced general availability of ES|QL in cross-cluster search (CCS) in this release. If you keep separate clusters for security logs and observability, one query can reach both. We stress that this concerns queries themselves and not LOOKUP JOIN, which in 9.1 works locally only. There is also logging of ES|QL queries to a file, useful for finding expensive queries, and an API that lists queries currently running (the latter is a technical preview, so it may still change).

How to put it to use

Start with one list that today lives in a spreadsheet or in administrators’ heads: system owners, partner addresses, known scanners. Load it into an index in lookup mode and build one query or Kibana dashboard that uses it. If that works, further lists take minutes.

If you plan to upgrade from 8.x to 9.1, remember that it requires moving to 8.19 first, and 8.19 already has the same LOOKUP JOIN as GA. So you can start using the new feature before the major upgrade.

We described rules that this kind of enrichment suits in SIEM on Elastic Security: first steps with detection rules. We choose data sources and reference lists as part of log management.