ES|QL conditional functions and expressions

Conditional functions return one of their arguments by evaluating in an if-else manner. ES|QL supports these conditional functions:

• CASE
• COALESCE
• GREATEST
• LEAST

Syntax

×

Parameters

condition

A condition.

trueValue

The value that’s returned when the corresponding condition is the first to evaluate to true. The default value is returned when no condition matches.

elseValue

The value that’s returned when no condition evaluates to true.

Description

Accepts pairs of conditions and values. The function returns the value that belongs to the first condition that evaluates to true. If the number of arguments is odd, the last argument is the default value which is returned when no condition matches. If the number of arguments is even, and no condition matches, the function returns null.

Supported types

Examples

Determine whether employees are monolingual, bilingual, or polyglot:

FROM employees
| EVAL type = CASE(
    languages <= 1, "monolingual",
    languages <= 2, "bilingual",
     "polyglot")
| KEEP emp_no, languages, type

Calculate the total connection success rate based on log messages:

FROM sample_data
| EVAL successful = CASE(
    STARTS_WITH(message, "Connected to"), 1,
    message == "Connection error", 0
  )
| STATS success_rate = AVG(successful)

Calculate an hourly error rate as a percentage of the total number of log messages:

FROM sample_data
| EVAL error = CASE(message LIKE "*error*", 1, 0)
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS error_rate = AVG(error) by hour
| SORT hour

Syntax

×

Parameters

first

Expression to evaluate.

rest

Other expression to evaluate.

Description

Returns the first of its arguments that is not null. If all arguments are null, it returns null.

Supported types

Example

ROW a=null, b="b"
| EVAL COALESCE(a, b)

Syntax

×

Parameters

first

First of the columns to evaluate.

rest

The rest of the columns to evaluate.

Description

Returns the maximum value from multiple columns. This is similar to MV_MAX except it is intended to run on multiple columns at once.

Supported types

Example

ROW a = 10, b = 20
| EVAL g = GREATEST(a, b)

Syntax

×

Parameters

first

First of the columns to evaluate.

rest

The rest of the columns to evaluate.

Description

Returns the minimum value from multiple columns. This is similar to MV_MIN except it is intended to run on multiple columns at once.

Supported types

Example

ROW a = 10, b = 20
| EVAL l = LEAST(a, b)