This documentation contains work-in-progress information for future Elastic Stack and Cloud releases. Use the version selector to view supported release docs. It also contains some Elastic Cloud serverless information. Check out our serverless docs for more details.

« Data processing with DISSECT and GROK Data enrichment »

› › ›

LOOKUP JOIN

edit

IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.

LOOKUP JOIN

edit

This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

The ES|QL LOOKUP join processing command combines data from your ES|QL query results table with matching records from a specified lookup index. It adds fields from the lookup index as new columns to your results table based on matching values in the join field.

Teams often have data scattered across multiple indices – like logs, IPs, user IDs, hosts, employees etc. Without a direct way to enrich or correlate each event with reference data, root-cause analysis, security checks, and operational insights become time-consuming.

For example, you can use LOOKUP JOIN to:

Retrieve environment or ownership details for each host to correlate your metrics data.
Quickly see if any source IPs match known malicious addresses.
Tag logs with the owning team or escalation info for faster triage and incident response.

Compare with ENRICH

edit

LOOKUP join is similar to ENRICH in the fact that they both help you join data together. You should use LOOKUP JOIN when:

Your enrichment data changes frequently
You want to avoid index-time processing
You want SQL-like behavior, so that multiple matches result in multiple rows
You need to match on any field in a lookup index
You use document or field level security
You want to restrict users to use only specific lookup indices
You do not need to match using ranges or spatial relations

How the command works

edit

The LOOKUP JOIN command adds fields from the lookup index as new columns to your results table based on matching values in the join field.

LOOKUP JOIN <lookup_index> ON <field_name>

The command requires two parameters:

lookup_index: The name of the lookup index. This must be a specific index name - wildcards, aliases, and remote cluster references are not supported. Indices used for lookups must be configured with the lookup mode.

field_name: The field to join on. This field must exist in both your current query results and in the lookup index. If the field contains multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).

If you’re familiar with SQL, LOOKUP JOIN has left-join behavior. This means that if no rows match in the lookup index, the incoming row is retained and null values are added. If many rows in the lookup index match, LOOKUP JOIN adds one row per match.

Example

edit

You can run this example for yourself to see how it works by setting up the indices and adding sample data. Otherwise, you just inspect the query and response.

Sample data

edit

Expand for setup instructions

Set up indices

First, let’s create two indices with mappings: threat_list and firewall_logs.

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

The lookup index must be set up using this mode

PUT firewall_logs
{
  "mappings": {
    "properties": {
      "timestamp": { "type": "date" },
      "source.ip": { "type": "ip" },
      "destination.ip": { "type": "ip" },
      "action": { "type": "keyword" },
      "bytes_transferred": { "type": "long" }
    }
  }
}

Add sample data

Next, let’s add some sample data to both indices. The threat_list index contains known malicious IPs, while the firewall_logs index contains logs of network traffic.

POST threat_list/_bulk
{"index":{}}
{"source.ip":"203.0.113.5","threat_level":"high","threat_type":"C2_SERVER","last_updated":"2025-04-22"}
{"index":{}}
{"source.ip":"198.51.100.2","threat_level":"medium","threat_type":"SCANNER","last_updated":"2025-04-23"}

POST firewall_logs/_bulk
{"index":{}}
{"timestamp":"2025-04-23T10:00:01Z","source.ip":"192.0.2.1","destination.ip":"10.0.0.100","action":"allow","bytes_transferred":1024}
{"index":{}}
{"timestamp":"2025-04-23T10:00:05Z","source.ip":"203.0.113.5","destination.ip":"10.0.0.55","action":"allow","bytes_transferred":2048}
{"index":{}}
{"timestamp":"2025-04-23T10:00:08Z","source.ip":"198.51.100.2","destination.ip":"10.0.0.200","action":"block","bytes_transferred":0}
{"index":{}}
{"timestamp":"2025-04-23T10:00:15Z","source.ip":"203.0.113.5","destination.ip":"10.0.0.44","action":"allow","bytes_transferred":4096}
{"index":{}}
{"timestamp":"2025-04-23T10:00:30Z","source.ip":"192.0.2.1","destination.ip":"10.0.0.100","action":"allow","bytes_transferred":512}

Query the Data

edit

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

	The source index
	The lookup index and join field
	Filter for rows with non-null threat levels
	LOOKUP JOIN does not guarantee output order, so you must explicitly sort
	Keep only relevant fields
	Limit the output to 10 rows

Response

edit

A successful query will output a table like this:

source.ip	action	threat_type	threat_level
203.0.113.5	allow	C2_SERVER	high
198.51.100.2	block	SCANNER	medium
203.0.113.5	allow	C2_SERVER	high

In this example, you can see that the source.ip field from the firewall_logs index is matched with the source.ip field in the threat_list index, and the corresponding threat_level and threat_type fields are added to the output.

Additional examples

edit

Refer to the examples section of the LOOKUP JOIN command reference for more examples.

Prerequisites

edit

To use LOOKUP JOIN, the following requirements must be met:

Indices used for lookups must be configured with the lookup mode
Compatible data types: The join key and join field in the lookup index must have compatible data types. This means:
- The data types must either be identical or be internally represented as the same type in ES|QL
- Numeric types follow these compatibility rules:
  - short and byte are compatible with integer (all represented as int)
  - float, half_float, and scaled_float are compatible with double (all represented as double)
- For text fields: You can only use text fields as the join key on the left-hand side of the join and only if they have a .keyword subfield

To obtain a join key with a compatible type, use a conversion function if needed.

For a complete list of supported data types and their internal representations, see the Supported Field Types documentation.

Usage notes

edit

This section covers important details about LOOKUP JOIN that impact query behavior and results. Review these details to ensure your queries work as expected and to troubleshoot unexpected results.

Handling name collisions

edit

When fields from the lookup index match existing column names, the new columns override the existing ones. Before the LOOKUP JOIN command, preserve columns by either:

Using RENAME to assign non-conflicting names
Using EVAL to create new columns with different names

Sorting behavior

edit

The output rows produced by LOOKUP JOIN can be in any order and may not respect preceding SORT commands. To guarantee a certain ordering, place a SORT after any LOOKUP JOIN commands.

Limitations

edit

The following are the current limitations with LOOKUP JOIN

Indices in lookup mode are always single-sharded.
Cross cluster search is unsupported initially. Both source and lookup indices must be local.
Currently, only matching on equality is supported.
LOOKUP JOIN can only use a single match field and a single index. Wildcards, aliases, datemath, and datastreams are not supported.
The name of the match field in LOOKUP JOIN lu++_++idx ON match++_++field must match an existing field in the query. This may require renames or evals to achieve.
The query will circuit break if there are too many matching documents in the lookup index, or if the documents are too large. More precisely, LOOKUP JOIN works in batches of, normally, about 10,000 rows; a large amount of heap space is needed if the matching documents from the lookup index for a batch are multiple megabytes or larger. This is roughly the same as for ENRICH.

« Data processing with DISSECT and GROK Data enrichment »