ES|QL LET directive
The LET directive binds a subquery to a name.
The rest of the query can then reference that name instead of repeating the subquery.
LET name = ( <query> ); [LET name = ( <query> ); ...] <query>
Each LET directive declares one named subquery and ends with a semicolon. A query can
declare several named subqueries, one LET directive each. When a query also uses
SET, all SET directives
come before the LET directives.
name- The subquery name. It must be a lowercase, unquoted identifier, and each name can
be declared only once in a query. Because a named subquery is read through
FROM, its name follows the same rules as an index name: it can't start with_, can't bemetadataorwith, and can't be longer than 255 bytes. <query>- The subquery body: a complete ES|QL query in parentheses, starting with a
FROM,TS, orROWsource command.
A named subquery can be referenced anywhere a subquery can read a source:
- In a
FROMcommand, on its own or alongside index patterns and subqueries, for exampleFROM top_langsorFROM languages, top_langs. Each named subquery referenced this way behaves like a subquery inFROM. - In an
INorNOT INsubquery, asIN (FROM name). A bareIN nameis not supported, andIN (name)is a one-element value list, not a reference to the named subquery. - In the body of a later
LETnamed subquery.
Each reference runs the named subquery's query again, and the results aren't shared between
references. A named subquery whose rows can change from one run to the next, for example one
that uses SAMPLE, or LIMIT without SORT, can return different rows at each
reference.
Named subqueries are resolved in the order they are declared: a subquery body can reference only named subqueries declared before it. A reference to a named subquery declared later, or to the named subquery itself, is an error.
Every named subquery must be referenced, either by the main query or by another named subquery that is itself referenced. Declaring a named subquery that is never referenced is an error.
A named subquery takes precedence over an index, alias, or view with the same name.
Only exact names are matched: an index pattern such as name* refers to indices,
not to the named subquery.
Named subqueries are local to the query that declares them. A
view referenced by the query does
not see them; a view body can declare its own LET named subqueries.
- A named subquery can't be the source of a
TScommand. - A named subquery can't be the right-hand side of a
LOOKUP JOIN.
LET top_langs = (FROM languages | SORT language_code | LIMIT 2);
FROM top_langs
| KEEP language_code, language_name
| SORT language_code
| language_code:integer | language_name:keyword |
|---|---|
| 1 | English |
| 2 | French |
LET filtered = (FROM languages | WHERE language_code > 1);
LET top2 = (FROM filtered | SORT language_code | LIMIT 2);
FROM top2
| KEEP language_code, language_name
| SORT language_code
| language_code:integer | language_name:keyword |
|---|---|
| 2 | French |
| 3 | Spanish |
LET low_codes = (FROM languages | WHERE language_code <= 2 | KEEP language_code);
FROM languages
| WHERE language_code IN (FROM low_codes)
| KEEP language_code, language_name
| SORT language_code
| language_code:integer | language_name:keyword |
|---|---|
| 1 | English |
| 2 | French |
With METADATA _class, _name,
rows read from a named subquery have _class set to subquery and _name set to the subquery name:
LET top_langs = (FROM languages | SORT language_code | LIMIT 2);
FROM languages, top_langs METADATA _class, _name
| STATS count = COUNT(*) BY _class, _name
| SORT _class
| count:long | _class:keyword | _name:keyword |
|---|---|---|
| 4 | index | languages |
| 2 | subquery | top_langs |