WHERE

Serverless Stack

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

Syntax

WHERE expression
		

Parameters

expression

A boolean expression.

Examples

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
		

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
		

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.

For NULL comparison, use the IS NULL and IS NOT NULL predicates.

Example

FROM employees
| WHERE birth_date IS NULL
		

Example

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

For matching text, you can use full text search functions like MATCH.

Use MATCH to perform a match query on a specified field.

Match can be used on text fields, as well as other field types like boolean, dates, and numeric types.

Examples

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

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

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

Example

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

Matching the exact characters * and . 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 LIKE "foo \\* bar"
		

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

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

Serverless Stack 9.1.0

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?")
		

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

Example

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

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"""
		

Serverless Stack 9.2.0

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.")
		

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)
		

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