Create lookup indices from Discover queries

In Discover, LOOKUP JOIN commands include interactive options that let you create or edit lookup indices directly from the editor. You can enter rows, upload a CSV file, and join the new index in the same query.

This page describes the Kibana editor. You can also create and manage indices with the create index API for Elastic Stack and for Elastic Cloud Serverless.

  • To create lookup indices, you need the create_index Elasticsearch privilege on the corresponding pattern.
  • To edit lookup indices, you need the write Elasticsearch privilege.
  • To view lookup indices in read-only mode, you need the view_index_metadata Elasticsearch privilege.
  • You need an ES|QL query in Discover. If you're new to ES|QL in Discover, start with Get started with ES|QL in Discover.

You can create a lookup index directly from the ES|QL editor. To populate this index, you can enter data manually or upload a CSV file up to 500 MB.

  1. In your ES|QL query, add a LOOKUP JOIN command. For example:

    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.

  2. Select the Create lookup index suggestion from the autocomplete menu.

  3. Enter a name for the lookup index. The name must follow these rules:

    • The name must not contain spaces or any of the following characters: \, /, *, ?, <, >, |, :, and #.
    • The name must not start with -, _, +, or ..
    • The name must be lowercase.

  4. Provide data for the lookup index. You can either:

    • Upload a CSV file up to 500 MB. 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 adding a field, you must set its name and data type.
      Note

      Some Elasticsearch data types aren't supported in Kibana.

    • Combine both methods. You can upload a file after adding data manually, and edit or expand the data imported from a file.

  5. Check your index and its data. You can explore your index using the search field, or open it in a new Discover session by selecting Open in Discover. If you open it in Discover, a new browser tab opens with a prefilled ES|QL query on the index.

    Tip

    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.

  6. Save any unsaved changes, then Close the index editor to return to your query.

Your new index is automatically added to your query. You can then specify the field to join using ON <field_to_join>.

When you are editing a lookup index from the ES|QL editor, you can add data to it by uploading CSV files up to 500 MB.

  1. Drag the files you want to upload from your computer. 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.

  2. 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.

  3. 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.

  4. 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.

  1. Select Upload file.

  2. 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.

  3. 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.

  4. Select Import to finalize the operation.

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

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:

  1. In the ES|QL query, hover over the lookup index name.

  2. Select the Edit lookup index or View lookup index option. A flyout showing the index opens.

  3. 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.

  4. If you made changes, select Save before closing the flyout.

At any time, you can delete all the index data and fields.

  1. Select all the index data using the checkbox in the header of the table.

  2. Select Delete selected from the contextual menu.

  3. Once all entries are deleted, a Reset index button appears. Select it to remove all fields configured in the index.

The lookup index is fully reset and saved automatically.

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.

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.