mirror of
https://github.com/elastic/elasticsearch.git
synced 2025-07-02 11:23:20 -04:00
4065510f38
* [DOCS][8.x] ESQL: Document LU JOIN/MV_EXPAND not respecting SORT * Fix nasty asciidoc backticks
116 lines
3.4 KiB
Text
116 lines
3.4 KiB
Text
[discrete]
|
|
[[esql-lookup-join]]
|
|
=== `LOOKUP JOIN`
|
|
|
|
[WARNING]
|
|
====
|
|
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.
|
|
====
|
|
|
|
`LOOKUP JOIN` enables you to add data from another index, AKA a 'lookup'
|
|
index, to your {esql} query results, simplifying data enrichment
|
|
and analysis workflows.
|
|
|
|
See <<esql-lookup-join-landing-page,the high-level landing page>> for an overview of the `LOOKUP JOIN` command, including use cases, prerequisites, and current limitations.
|
|
|
|
*Syntax*
|
|
|
|
[source,esql]
|
|
----
|
|
FROM <source_index>
|
|
| LOOKUP JOIN <lookup_index> ON <field_name>
|
|
----
|
|
|
|
*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` <<index-mode-setting,index mode setting>>.
|
|
|
|
`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).
|
|
|
|
*Description*
|
|
|
|
The `LOOKUP JOIN` command adds new columns to your {esql} 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 field, 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 <<esql-lookup-join-usage-notes,Usage notes>>.
|
|
====
|
|
|
|
*Examples*
|
|
|
|
*IP Threat correlation*: This query would allow you to see if any source
|
|
IPs match known malicious addresses.
|
|
|
|
[source,esql]
|
|
----
|
|
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:
|
|
|
|
[source,esql]
|
|
----
|
|
FROM firewall_logs
|
|
| LOOKUP JOIN threat_list ON source.IP
|
|
| WHERE threat_level IS NOT NULL
|
|
----
|
|
|
|
*Host metadata correlation*: This query pulls in environment or
|
|
ownership details for each host to correlate with your metrics data.
|
|
|
|
[source,esql]
|
|
----
|
|
FROM system_metrics
|
|
| LOOKUP JOIN host_inventory ON host.name
|
|
| LOOKUP JOIN employees ON host.name
|
|
----
|
|
|
|
*Service ownership mapping*: This query would show logs with the owning
|
|
team or escalation information for faster triage and incident response.
|
|
|
|
[source,esql]
|
|
----
|
|
FROM app_logs
|
|
| LOOKUP JOIN service_owners ON service_id
|
|
----
|
|
|
|
`LOOKUP JOIN` is generally faster when there are fewer rows to join
|
|
with. {esql} will try and perform any `WHERE` clause before the
|
|
`LOOKUP JOIN` where possible.
|
|
|
|
The two following examples will have the same results. The two examples
|
|
have the `WHERE` clause before and after the `LOOKUP JOIN`. It does not
|
|
matter how you write your query, our optimizer will move the filter
|
|
before the lookup when ran.
|
|
|
|
[source,esql]
|
|
----
|
|
FROM Left
|
|
| WHERE Language IS NOT NULL
|
|
| LOOKUP JOIN Right ON Key
|
|
----
|
|
|
|
[source,esql]
|
|
----
|
|
FROM Left
|
|
| LOOKUP JOIN Right ON Key
|
|
| WHERE Language IS NOT NULL
|
|
----
|