ES|QL LOOKUP JOIN command

LOOKUP JOIN enables you to add data from another index, AKA a 'lookup' index, to your ES|QL query results, simplifying data enrichment and analysis workflows.

Refer to the high-level landing page for an overview of the LOOKUP JOIN command, including use cases, prerequisites, and current limitations.

Syntax

FROM <source_index>
| LOOKUP JOIN <lookup_index> ON <join_condition>

The name of the lookup index. This must be a specific index name or alias. Wildcards and remote cluster prefixes are not supported. If the query source includes remote indices, the lookup index must exist on all involved clusters. Indices used for lookups must be configured with the lookup index mode. Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Serverless Elasticsearch Vector Database projects.

In cross-cluster or cross-project queries, you can prefix the index name with _coordinator: to run the lookup on the coordinating node of the local cluster or origin project instead of on each remote cluster or linked project. This allows LOOKUP JOIN to be used after pipeline-breaking commands such as STATS, LIMIT, SORT. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Generally available This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments.

<join_condition>

Can be one of the following:

A single field name
A comma-separated list of field names, for example <field1>, <field2>, <field3> The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.2 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
An expression with one or more predicates linked by AND, for example <left_field1> >= <lookup_field1> AND <left_field2> == <lookup_field2>. Each predicate compares a field from the left index with a field from the lookup index using binary operators (==, >=, <=, >, <, !=). Each field name in the join condition must exist in only one of the indexes. Use RENAME to resolve naming conflicts. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.2 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Preview This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Serverless interfaces and procedures might differ from classic Elastic Stack deployments.
An expression that includes full text functions and other Lucene-pushable functions, for example MATCH(<lookup_field>, "search term") AND <left_field> == <lookup_field>. These functions can be combined with binary operators and logical operators (AND, OR, NOT) to create complex join conditions. At least one condition that relates the lookup index fields to the left side of the join fields is still required. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.3 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Preview This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Serverless interfaces and procedures might differ from classic Elastic Stack deployments.

If using join on a single field or a field list, the fields used must exist in both your current query results and in the lookup index. If the fields contains multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).

Description

The LOOKUP JOIN command adds new columns to your ES|QL query results table by finding documents in a lookup index that share the same join field value as your result rows.

For each row in your results table that matches a document in the lookup index based on the join fields, all fields from the matching document are added as new columns to that row.

If multiple documents in the lookup index match a single row in your results, the output will contain one row for each matching combination.

Tip

For important information about using LOOKUP JOIN, refer to Usage notes.

Supported types

field from the left index	field from the lookup index
boolean	boolean
byte	byte, short, integer, long, half_float, float, double, scaled_float
date	date
date_nanos	date_nanos
double	half_float, float, double, scaled_float, byte, short, integer, long
float	half_float, float, double, scaled_float, byte, short, integer, long
half_float	half_float, float, double, scaled_float, byte, short, integer, long
integer	byte, short, integer, long, half_float, float, double, scaled_float
ip	ip
keyword	keyword
long	byte, short, integer, long, half_float, float, double, scaled_float
scaled_float	half_float, float, double, scaled_float, byte, short, integer, long
short	byte, short, integer, long, half_float, float, double, scaled_float
text	keyword

Examples

The following examples show common LOOKUP JOIN use cases.

IP threat correlation

Check whether source IPs match known malicious addresses:

FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP

To filter only for those rows that have a matching threat_list entry, use WHERE ... IS NOT NULL with a field from the lookup index:

FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
| WHERE threat_level IS NOT NULL

Host metadata correlation

Pull in environment or ownership details for each host to correlate with metrics data:

FROM system_metrics
| LOOKUP JOIN host_inventory ON host.name
| LOOKUP JOIN ownerships ON host.name

Service ownership mapping

Show logs alongside the owning team or escalation information for faster triage:

FROM app_logs
| LOOKUP JOIN service_owners ON service_id

Filter before LOOKUP JOIN

LOOKUP JOIN is generally faster when there are fewer rows to join with. ES|QL will try to perform any WHERE clause before the LOOKUP JOIN where possible. The following two queries produce the same results. One filters before the join, the other after. The optimizer will push the filter before the lookup when possible:

FROM employees
| EVAL language_code = languages
| WHERE emp_no >= 10091 AND emp_no < 10094
| LOOKUP JOIN languages_lookup ON language_code

emp_no:integer	language_code:integer	language_name:keyword
10091	3	Spanish
10092	1	English
10093	3	Spanish

FROM employees
| EVAL language_code = languages
| LOOKUP JOIN languages_lookup ON language_code
| WHERE emp_no >= 10091 AND emp_no < 10094

emp_no:integer	language_code:integer	language_name:keyword
10091	3	Spanish
10092	1	English
10093	3	Spanish