elasticsearch/docs/reference/esql/processing-commands/lookup.asciidoc

[discrete]
[[esql-lookup]]
=== `LOOKUP`

experimental::["LOOKUP is a highly experimental and only available in SNAPSHOT versions."]

`LOOKUP` matches values from the input against a `table` provided in the request,
adding the other fields from the `table` to the output.

**Syntax**

[source,esql]
----
LOOKUP table ON match_field1[, match_field2, ...]
----

*Parameters*

`table`::
The name of the `table` provided in the request to match.
If the table's column names conflict with existing columns, the existing columns will be dropped.

`match_field`::
The fields in the input to match against the table.

*Examples*

// tag::examples[]
[source,console,id=esql-lookup-example]
----
POST /_query?format=txt
{
  "query": """
      FROM library
    | SORT page_count DESC
    | KEEP name, author
    | LOOKUP era ON author
    | LIMIT 5
  """,
  "tables": {
    "era": {
      "author": {"keyword": ["Frank Herbert", "Peter F. Hamilton", "Vernor Vinge", "Alastair Reynolds", "James S.A. Corey"]},
      "era":    {"keyword": [ "The New Wave",           "Diamond",      "Diamond",           "Diamond",           "Hadron"]}
    }
  }
}
----
// TEST[setup:library]

Which returns:

[source,text]
----
        name        |     author      |      era
--------------------+-----------------+---------------
Pandora's Star      |Peter F. Hamilton|Diamond
A Fire Upon the Deep|Vernor Vinge     |Diamond
Dune                |Frank Herbert    |The New Wave
Revelation Space    |Alastair Reynolds|Diamond
Leviathan Wakes     |James S.A. Corey |Hadron
----
// TESTRESPONSE[s/\|/\\|/ s/\+/\\+/]
// TESTRESPONSE[non_json]
// end::examples[]