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 Planned

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.