# Join data from multiple indices with `LOOKUP JOIN` The ES|QL [`LOOKUP JOIN`](/content/docs/reference/query-languages/esql/commands/lookup-join/index.html) 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`](/content/docs/reference/query-languages/esql/esql-lookup-join#compare-with-enrich/index.html) [`LOOKUP JOIN`](/content/docs/reference/query-languages/esql/commands/lookup-join/index.html) is similar to [`ENRICH`](/content/docs/reference/query-languages/esql/commands/enrich/index.html) 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 ## [Syntax reference](/content/docs/reference/query-languages/esql/esql-lookup-join#syntax-reference/index.html) Refer to [`LOOKUP JOIN`](/content/docs/reference/query-languages/esql/commands/lookup-join/index.html) for the detailed syntax reference. ## [How the command works](/content/docs/reference/query-languages/esql/esql-lookup-join#esql-how-lookup-join-works/index.html) 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. The command requires two parameters: - The name of the lookup index (which must have the `lookup` [`index.mode setting`](/content/docs/reference/elasticsearch/index-settings/index-modules#index-mode-setting/index.html)) - The join condition. Can be one of the following: - A single field name - A comma-separated list of field names - An expression with one or more join conditions linked by `AND`. - An expression that includes [Full Text Functions](/content/docs/reference/query-languages/esql/functions-operators/search-functions/index.html) and other Lucene pushable functions applied to fields from the lookup index ```esql LOOKUP JOIN ON LOOKUP JOIN ON , , LOOKUP JOIN ON >= AND == LOOKUP JOIN ON MATCH(lookup_field, "search term") AND == ``` 1. Join on a single field 2. Join on multiple fields 3. Join on expression 4. Join with Full Text Functions 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`s are added. If many rows in the lookup index match, `LOOKUP JOIN` adds one row per match. ### [Cross-cluster support](/content/docs/reference/query-languages/esql/esql-lookup-join#cross-cluster-support/index.html) Remote lookup joins are supported in [cross-cluster queries](/content/docs/reference/query-languages/esql/esql-cross-clusters/index.html). The lookup index must exist on _all_ remote clusters being queried, because each cluster uses its local lookup index data. This follows the same pattern as [remote mode Enrich](/content/docs/reference/query-languages/esql/esql-cross-clusters#esql-enrich-remote/index.html). ```esql FROM log-cluster-*:logs-* | LOOKUP JOIN hosts ON source.ip ``` ### [Example](/content/docs/reference/query-languages/esql/esql-lookup-join#example/index.html) You can run this example for yourself if you'd like to see how it works, by setting up the indices and adding sample data. ### [Sample data](/content/docs/reference/query-languages/esql/esql-lookup-join#sample-data/index.html) **Set up indices** First let's create two indices with mappings: `threat_list` and `firewall_logs`. ```apiheader 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" } } } } ``` ```apiheader 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. ```apiheader 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"} ``` ```apiheader 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](/content/docs/reference/query-languages/esql/esql-lookup-join#query-the-data/index.html) ```esql FROM firewall_logs | LOOKUP JOIN threat_list ON source.ip | WHERE threat_level IS NOT NULL | SORT timestamp | KEEP source.ip, action, threat_type, threat_level | LIMIT 10 ``` 1. The source index 2. The lookup index and join field 3. Filter for rows non-null threat levels 4. LOOKUP JOIN does not guarantee output order, so you must explicitly sort the results if needed 5. Keep only relevant fields 6. Limit the output to 10 rows ### [Response](/content/docs/reference/query-languages/esql/esql-lookup-join#response/index.html) A successful query will output a table. 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. | 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 | ### [Limitations](/content/docs/reference/query-languages/esql/esql-lookup-join#limitations/index.html) The following are the current limitations with `LOOKUP JOIN`: - Indices in [`lookup` mode](/content/docs/reference/elasticsearch/index-settings/index-modules#index-mode-setting/index.html) are always single-sharded. - Cross cluster search is unsupported in versions prior to `9.2.0`. Both source and lookup indices must be local for these versions. - Currently, only matching on equality is supported. - In Stack versions `9.0-9.1`, `LOOKUP JOIN` can only use a single match field and a single index. Wildcards are not supported.