ES|QL WHERE command

The WHERE processing command produces a table that contains all the rows from the input table for which the provided condition evaluates to true.

Tip

In case of value exclusions, fields with null values will be excluded from search results. In this context a null means either there is an explicit null value in the document or there is no value at all. For example: WHERE field != "value" will be interpreted as WHERE field != "value" AND field IS NOT NULL.

For more information, refer to Working with NULL values in ES|QL.

Syntax

WHERE expression

Parameters

expression: A boolean expression.

Examples

The following examples show common ways to filter rows with WHERE.

Filter rows by field value

FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired == true

Which, if still_hired is a boolean field, can be simplified to:

FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired

Filter by time range

Use date math to retrieve data from a specific time range. For example, to retrieve the last hour of logs:

FROM sample_data
| WHERE @timestamp > NOW() - 1 hour

Use WHERE with a function

WHERE supports various functions. For example the LENGTH function:

FROM employees
| KEEP first_name, last_name, height
| WHERE LENGTH(first_name) < 4

For a complete list of all functions, refer to Functions overview.

NULL Predicates

For NULL comparison, use the IS NULL and IS NOT NULL predicates. To learn how NULL affects WHERE, comparisons, and boolean logic, refer to Working with NULL values in ES|QL.

Example

FROM employees
| WHERE birth_date IS NULL

first_name:keyword	last_name:keyword
Basil	Tramer
Florian	Syrotiuk
Lucien	Rosenbaum

Example

FROM employees
| WHERE is_rehired IS NOT NULL
| STATS COUNT(emp_no)

COUNT(emp_no):long
84

Matching text

Use MATCH to perform a match query on a specified field. MATCH works on text fields as well as boolean, date, and numeric types.

Examples

FROM books
| WHERE MATCH(author, "Faulkner")

book_no:keyword	author:text
2378	[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott]
2713	William Faulkner
2847	Colleen Faulkner
2883	William Faulkner
3293	Danny Faulkner

FROM books
| WHERE MATCH(title, "Hobbit Back Again", {"operator": "AND"})
| KEEP title;

title:text
The Hobbit or There and Back Again

Tip

You can also use the shorthand match operator : instead of MATCH.

LIKE and RLIKE

Use LIKE to filter data based on string patterns using wildcards. LIKE usually acts on a field placed on the left-hand side of the operator, but it can also act on a constant (literal) expression. The right-hand side of the operator represents the pattern.

The following wildcard characters are supported:

* matches zero or more characters.
? matches one character.

Supported types

str	pattern	result
keyword	keyword	boolean
text	keyword	boolean

Example

FROM employees
| WHERE first_name LIKE """?b*"""
| KEEP first_name, last_name

first_name:keyword	last_name:keyword
Ebbe	Callaway
Eberhardt	Terkki

When used on text fields, LIKE treats the field as a keyword and does not use the analyzer. This means the pattern matching is case-sensitive and must match the exact string indexed. To perform full-text search, use the MATCH or QSTR functions.

Matching the exact characters * and ? will require escaping. The escape character is backslash \. Since backslash is also special in string literals, it will require further escaping. To match a literal backslash in the data, write \\ in the pattern (escape plus backslash). Windows-style paths such as C:\Windows\System32 are much easier with triple-quoted string literals (three consecutive " characters as delimiters): the value can contain single backslashes as in C:\Windows\..., and a pattern that matches C:\Windows followed by anything uses \\ before Windows inside that literal (see the example below). When you send ES|QL inside a JSON request body (for example with cURL), JSON string escaping adds another layer: each backslash in the ES|QL text is often written as \\ in JSON, so a single literal backslash in the pattern can require many backslashes by the time it reaches the parser.

ROW message = "foo * bar"
| WHERE message LIKE "foo \\* bar"

To reduce the overhead of escaping, we suggest using triple quotes strings """

ROW message = "foo * bar"
| WHERE message LIKE """foo \* bar"""

ROW path = """C:\Windows\System32\foo.txt"""
| WHERE path LIKE """C:\\Windows*"""

Both a single pattern or a list of patterns are supported. If a list of patterns is provided, the expression will return true if any of the patterns match.

ROW message = "foobar"
| WHERE message like ("foo*", "bar?")

Patterns may be specified with REST query placeholders as well

FROM employees
| WHERE first_name LIKE ?pattern
| KEEP first_name, last_name

The pattern can also be any constant expression, such as a call to CONCAT or TO_LOWER.

from employees | where first_name like concat("Eber", "*") | keep emp_no, first_name

Use RLIKE to filter data based on string patterns using using regular expressions. RLIKE usually acts on a field placed on the left-hand side of the operator, but it can also act on a constant (literal) expression. The right-hand side of the operator represents the pattern.

Supported types

str	pattern	result
keyword	keyword	boolean
text	keyword	boolean

Example

FROM employees
| WHERE first_name RLIKE """.leja.*"""
| KEEP first_name, last_name

first_name:keyword	last_name:keyword
Alejandro	McAlpine

When used on text fields, RLIKE treats the field as a keyword and does not use the analyzer. This means the pattern matching is case-sensitive and must match the exact string indexed. To perform full-text search, use the MATCH or QSTR functions.

Matching special characters (eg. ., *, (...) will require escaping. The escape character is backslash \. Since also backslash is a special character in string literals, it will require further escaping.

ROW message = "foo ( bar"
| WHERE message RLIKE "foo \\( bar"

To reduce the overhead of escaping, we suggest using triple quotes strings """

ROW message = "foo ( bar"
| WHERE message RLIKE """foo \( bar"""

Both a single pattern or a list of patterns are supported. If a list of patterns is provided, the expression will return true if any of the patterns match.

ROW message = "foobar"
| WHERE message RLIKE ("foo.*", "bar.")

Patterns may be specified with REST query placeholders as well

FROM employees
| WHERE first_name RLIKE ?pattern
| KEEP first_name, last_name

The pattern can also be any constant expression, such as a call to CONCAT or TO_UPPER.

from employees | where first_name rlike concat("Eber", ".*") | keep emp_no, first_name

IN

The IN operator allows testing whether a field or expression equals an element in a list of literals, fields or expressions:

Example

ROW a = 1, b = 4, c = 3
| WHERE c-a IN (3, b / 2, a)

a:integer	b:integer	c:integer
1	4	3

You can also use a subquery on the right-hand side of IN or NOT IN to filter rows against the results of another query, including a multi-column (a, b) IN (...) tuple. To learn more, refer to Use subqueries with IN and NOT IN.

For a complete list of all operators, refer to Operators.