Create lookup indices from Discover queries

If you want to enrich your query results with extra data, such as a team name for each host, you can create a small lookup index and join it to your query without leaving Discover. A lookup index is an Elasticsearch index that a LOOKUP JOIN command matches against your results. You can fill it by hand or from a CSV file.

To create a lookup index outside Discover, use the create index API for Elastic Stack or Elastic Cloud Serverless. Set the index.mode index setting to lookup, because LOOKUP JOIN requires it.

Before you begin

To create lookup indices, you need the create_index Elasticsearch privilege on the corresponding pattern. To add data to the new index, you also need the write privilege.
To edit lookup indices, you need the write Elasticsearch privilege.
To view lookup indices in read-only mode, you need the read Elasticsearch privilege.
To reset the configuration of a lookup index, you need the delete_index and create_index Elasticsearch privileges.
Make sure Discover is in ES|QL mode. Classic mode doesn't support this functionality. To switch, refer to Switch to Discover's ES|QL mode.
If you're new to ES|QL in Discover, start with Get started with ES|QL in Discover.

Create and edit lookup indices from queries

Create a lookup index from the editor

You can create a lookup index directly from the ES|QL editor and fill it by hand or from a CSV file.

In your ES|QL query, add a LOOKUP JOIN command. For example:
ES|QL
```
FROM kibana_sample_data_logs
| LOOKUP JOIN
```
Add a space after the command. The editor suggests existing lookup indices and offers to create one. You can also enter an index name in your query. If it doesn't exist, the editor suggests creating it.
Select the Create lookup index suggestion from the autocomplete menu.
Enter a name for the lookup index. Use a lowercase name with no spaces and none of these characters: \, /, *, ?, <, >, |, :, #. The name can't start with -, _, +, or ., and it can't match an existing index.
Provide data for the lookup index in one of these ways, or combine them:
- Upload a CSV file. The default size limit is 500 MB, or 100 MB in 9.2 and 9.3. On Elastic Stack, the fileUpload:maxFileSize advanced setting changes the limit. When you upload a file, you can preview its data, inspect its contents, and review any detected issues before importing it. Refer to Create lookup indices from Discover queries > Load data into a lookup index from a CSV file for more details.
- Add data manually. You can add fields and populate data directly. When you add a field:
  - 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. 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. Set its name and data type. You can't change the data type after you save the lookup index. The type list offers only some Elasticsearch data types. If a type isn't in the list, the editor doesn't support it. For example, you can't select object, nested, alias, or scaled_float.
  - The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview in 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. Set its name. Elasticsearch sets the data type from the values you enter.
You can upload a file after adding data manually, and edit or expand the data imported from a file.
Check your index and its data. The search field and Open in Discover become available after you save the index for the first time, so save it first if you want to use them. The search field filters the rows of the index. Open in Discover opens the index with a prefilled ES|QL query. From 9.3, it opens in a new Discover tab, after the editor closes and asks you about any unsaved changes. In 9.2, it opens in a new browser tab.

Tip 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. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.5 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.

The search field supports free text and Kibana Query Language (KQL) syntax, with autocomplete for field names and values. Newly added columns appear as autocomplete suggestions only after you save the index, and the search doesn't match unsaved values.
Select Save (or Save and close) for any unsaved changes. The editor creates the index the first time you save. Then select Close to return to your query.

Result: The editor adds the new index to your query. To finish the join, name the field to match with ON <field_to_join>.

Load data into a lookup index from a CSV file

When you are editing a lookup index from the ES|QL editor, you can add data to it by uploading CSV files. The default size limit is 500 MB, or 100 MB in 9.2 and 9.3. Refer to Create lookup indices from Discover queries > Create a lookup index from the editor for how to change it.

Serverless Preview Stack Preview 9.3+

Drag the files you want to upload from your computer, or select the uploading link in the empty index prompt to browse for them. You can add several files at a time and can repeat the operation multiple times.

Note

If your index has unsaved changes, a message tells you that you lose these changes. To keep these changes, cancel the upload and save your index, then start a new upload.
Preview the data for each file you're importing, then select Continue. If the editor detects issues, a message appears with more details. Typical issues include differences between the fields of the index and those of the imported files.
- The editor adds new fields from the imported files to the index.
- The editor keeps fields that exist in the index but not in the imported file, and leaves them empty.
Review and adjust the field names and data types to match the needs of your lookup index. After the import, you can no longer edit them.
Select Import to validate the configuration and proceed with the import, then Finish to finalize the operation and return to the lookup index.

Data coming from the files is appended to the index, and the index is automatically saved.

Stack Preview 9.2

Select Upload file.
Select the CSV file to import on your machine. You can select several files to import at once.

Note

If your index has unsaved changes, a message tells you that you lose these changes. To keep these changes, cancel the upload and save your index, then select Upload file again.
Preview the data for each file you're importing. The editor detects and sets the field data types. If it detects issues, a File issues tab with more details appears before you validate the import. Common issues include differences between the fields in the index and in the imported files.
- The editor adds new fields from the imported files to the index.
- The editor keeps fields that exist in the index but not in the imported file, and leaves them empty.
Select Import to finalize the operation.

Data coming from the files is appended to the index, and the index is automatically saved.

View or edit a lookup index from the editor

You can view and edit existing lookup indices referenced in an ES|QL query directly from the editor, depending on your privileges.

To view or edit an index:

In the ES|QL query, hover over the lookup index name.
Select the Edit lookup index or View lookup index option. The editor offers Edit lookup index if you have the write privilege, and View lookup index if you only have the read privilege. A flyout showing the index opens.
Depending on your permissions and needs, explore or edit the index. When editing the index, you have the same options described in Create lookup indices from Discover queries > Create a lookup index from the editor.

Note

Editing a lookup index affects all ES|QL queries that reference it. Make sure that your changes are compatible with existing queries that use this index.
If you made changes, select Save before closing the flyout.

Reset the lookup index configuration

At any time, you can delete all the index data and fields. A reset deletes the index and creates it again as an empty lookup index, so it needs the delete_index and create_index privileges.

Serverless Preview Stack Preview 9.3+

Select all the index data using the checkbox in the header of the table.
Select Delete selected from the contextual menu.
After you delete all entries, a Reset index button appears if you have the privileges for a reset. Select it, then confirm, to remove all fields configured in the index.

The lookup index is fully reset and saved automatically.

Stack Preview 9.2

In this version, you can't fully reset the index configuration. For example, you can't remove columns. However, you can delete the index data. To do that, select the entries to delete, then select Delete selected from the contextual menu.

Limitations

The following limitations apply to the lookup index editor in Kibana. For general limitations of the LOOKUP JOIN command, refer to Join data from multiple indices with LOOKUP JOIN.

Row display limit: The lookup index editor displays up to 1,000 rows. To find a specific row when the index contains more than 1,000 entries, use the search field: it searches the full index. The LIMIT command in your ES|QL query has no effect on the data shown here.