﻿---
title: ES|QL conditional functions and expressions
description: Conditional functions return one of their arguments by evaluating in an if-else manner. ES|QL supports these conditional functions: 
url: https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available
---

# 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`](#esql-case)
- [`COALESCE`](#esql-coalesce)
- [`GREATEST`](#esql-greatest)
- [`LEAST`](#esql-least)
- [`CLAMP`](#esql-clamp) <applies-to>Elastic Stack: Preview since 9.3</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`CLAMP_MIN`](#esql-clamp_min) <applies-to>Elastic Stack: Preview since 9.3</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`CLAMP_MAX`](#esql-clamp_max) <applies-to>Elastic Stack: Preview since 9.3</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>


## `CASE`

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/case.svg)

**Parameters**
<definitions>
  <definition term="condition">
    A condition.
  </definition>
  <definition term="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.
  </definition>
  <definition term="elseValue">
    The value that’s returned when no condition evaluates to `true`.
  </definition>
</definitions>

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

| condition | trueValue               | elseValue               | result                  |
|-----------|-------------------------|-------------------------|-------------------------|
| boolean   | aggregate_metric_double | aggregate_metric_double | aggregate_metric_double |
| boolean   | aggregate_metric_double |                         | aggregate_metric_double |
| boolean   | boolean                 | boolean                 | boolean                 |
| boolean   | boolean                 |                         | boolean                 |
| boolean   | cartesian_point         | cartesian_point         | cartesian_point         |
| boolean   | cartesian_point         |                         | cartesian_point         |
| boolean   | cartesian_shape         | cartesian_shape         | cartesian_shape         |
| boolean   | cartesian_shape         |                         | cartesian_shape         |
| boolean   | date                    | date                    | date                    |
| boolean   | date                    |                         | date                    |
| boolean   | date_nanos              | date_nanos              | date_nanos              |
| boolean   | date_nanos              |                         | date_nanos              |
| boolean   | dense_vector            | dense_vector            | dense_vector            |
| boolean   | dense_vector            |                         | dense_vector            |
| boolean   | double                  | double                  | double                  |
| boolean   | double                  |                         | double                  |
| boolean   | exponential_histogram   | exponential_histogram   | exponential_histogram   |
| boolean   | exponential_histogram   |                         | exponential_histogram   |
| boolean   | geo_point               | geo_point               | geo_point               |
| boolean   | geo_point               |                         | geo_point               |
| boolean   | geo_shape               | geo_shape               | geo_shape               |
| boolean   | geo_shape               |                         | geo_shape               |
| boolean   | geohash                 | geohash                 | geohash                 |
| boolean   | geohash                 |                         | geohash                 |
| boolean   | geohex                  | geohex                  | geohex                  |
| boolean   | geohex                  |                         | geohex                  |
| boolean   | geotile                 | geotile                 | geotile                 |
| boolean   | geotile                 |                         | geotile                 |
| boolean   | histogram               | histogram               | histogram               |
| boolean   | histogram               |                         | histogram               |
| boolean   | integer                 | integer                 | integer                 |
| boolean   | integer                 |                         | integer                 |
| boolean   | ip                      | ip                      | ip                      |
| boolean   | ip                      |                         | ip                      |
| boolean   | keyword                 | keyword                 | keyword                 |
| boolean   | keyword                 | text                    | keyword                 |
| boolean   | keyword                 |                         | keyword                 |
| boolean   | long                    | long                    | long                    |
| boolean   | long                    |                         | long                    |
| boolean   | tdigest                 | tdigest                 | tdigest                 |
| boolean   | tdigest                 |                         | tdigest                 |
| boolean   | text                    | keyword                 | keyword                 |
| boolean   | text                    | text                    | keyword                 |
| boolean   | text                    |                         | keyword                 |
| boolean   | unsigned_long           | unsigned_long           | unsigned_long           |
| boolean   | unsigned_long           |                         | unsigned_long           |
| boolean   | version                 | version                 | version                 |
| boolean   | version                 |                         | version                 |

**Examples**
Determine whether employees are monolingual, bilingual, or polyglot:
```esql
FROM employees
| EVAL type = CASE(
    languages <= 1, "monolingual",
    languages <= 2, "bilingual",
     "polyglot")
| KEEP emp_no, languages, type
```


| emp_no:integer | languages:integer | type:keyword |
|----------------|-------------------|--------------|
| 10001          | 2                 | bilingual    |
| 10002          | 5                 | polyglot     |
| 10003          | 4                 | polyglot     |
| 10004          | 5                 | polyglot     |
| 10005          | 1                 | monolingual  |

Calculate the total connection success rate based on log messages:
```esql
FROM sample_data
| EVAL successful = CASE(
    STARTS_WITH(message, "Connected to"), 1,
    message == "Connection error", 0
  )
| STATS success_rate = AVG(successful)
```


| success_rate:double |
|---------------------|
| 0.5                 |

Calculate an hourly error rate as a percentage of the total number of log messages:
```esql
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
```


| error_rate:double | hour:date                |
|-------------------|--------------------------|
| 0.0               | 2023-10-23T12:00:00.000Z |
| 0.6               | 2023-10-23T13:00:00.000Z |


## `COALESCE`

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/coalesce.svg)

**Parameters**
<definitions>
  <definition term="first">
    Expression to evaluate.
  </definition>
  <definition term="rest">
    Other expression to evaluate.
  </definition>
</definitions>

**Description**
Returns the first of its arguments that is not null. If all arguments are null, it returns `null`.
**Supported types**

| first                 | rest                  | result                |
|-----------------------|-----------------------|-----------------------|
| boolean               | boolean               | boolean               |
| boolean               |                       | boolean               |
| cartesian_point       | cartesian_point       | cartesian_point       |
| cartesian_shape       | cartesian_shape       | cartesian_shape       |
| date                  | date                  | date                  |
| date_nanos            | date_nanos            | date_nanos            |
| exponential_histogram | exponential_histogram | exponential_histogram |
| geo_point             | geo_point             | geo_point             |
| geo_shape             | geo_shape             | geo_shape             |
| geohash               | geohash               | geohash               |
| geohex                | geohex                | geohex                |
| geotile               | geotile               | geotile               |
| histogram             | histogram             | histogram             |
| integer               | integer               | integer               |
| integer               |                       | integer               |
| ip                    | ip                    | ip                    |
| keyword               | keyword               | keyword               |
| keyword               |                       | keyword               |
| long                  | long                  | long                  |
| long                  |                       | long                  |
| tdigest               | tdigest               | tdigest               |
| text                  | text                  | keyword               |
| text                  |                       | keyword               |
| version               | version               | version               |

**Example**
```esql
ROW a=null, b="b"
| EVAL COALESCE(a, b)
```


| a:null | b:keyword | COALESCE(a, b):keyword |
|--------|-----------|------------------------|
| null   | b         | b                      |


## `GREATEST`

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/greatest.svg)

**Parameters**
<definitions>
  <definition term="first">
    First of the columns to evaluate.
  </definition>
  <definition term="rest">
    The rest of the columns to evaluate.
  </definition>
</definitions>

**Description**
Returns the maximum value from multiple columns. This is similar to [`MV_MAX`](/elastic/docs-builder/docs/2649/reference/query-languages/esql/functions-operators/mv-functions#esql-mv_max) except it is intended to run on multiple columns at once.
<note>
  When run on `keyword` or `text` fields, this returns the last string in alphabetical order. When run on `boolean` columns this will return `true` if any values are `true`.
</note>

**Supported types**

| first      | rest       | result     |
|------------|------------|------------|
| boolean    | boolean    | boolean    |
| boolean    |            | boolean    |
| date       | date       | date       |
| date_nanos | date_nanos | date_nanos |
| double     | double     | double     |
| integer    | integer    | integer    |
| integer    |            | integer    |
| ip         | ip         | ip         |
| keyword    | keyword    | keyword    |
| keyword    |            | keyword    |
| long       | long       | long       |
| long       |            | long       |
| text       | text       | keyword    |
| text       |            | keyword    |
| version    | version    | version    |

**Example**
```esql
ROW a = 10, b = 20
| EVAL g = GREATEST(a, b)
```


| a:integer | b:integer | g:integer |
|-----------|-----------|-----------|
| 10        | 20        | 20        |


## `LEAST`

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/least.svg)

**Parameters**
<definitions>
  <definition term="first">
    First of the columns to evaluate.
  </definition>
  <definition term="rest">
    The rest of the columns to evaluate.
  </definition>
</definitions>

**Description**
Returns the minimum value from multiple columns. This is similar to [`MV_MIN`](/elastic/docs-builder/docs/2649/reference/query-languages/esql/functions-operators/mv-functions#esql-mv_min) except it is intended to run on multiple columns at once.
**Supported types**

| first      | rest       | result     |
|------------|------------|------------|
| boolean    | boolean    | boolean    |
| boolean    |            | boolean    |
| date       | date       | date       |
| date_nanos | date_nanos | date_nanos |
| double     | double     | double     |
| integer    | integer    | integer    |
| integer    |            | integer    |
| ip         | ip         | ip         |
| keyword    | keyword    | keyword    |
| keyword    |            | keyword    |
| long       | long       | long       |
| long       |            | long       |
| text       | text       | keyword    |
| text       |            | keyword    |
| version    | version    | version    |

**Example**
```esql
ROW a = 10, b = 20
| EVAL l = LEAST(a, b)
```


| a:integer | b:integer | l:integer |
|-----------|-----------|-----------|
| 10        | 20        | 10        |


## `CLAMP`

<applies-to>
  - Elastic Stack: Preview since 9.3
</applies-to>

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/clamp.svg)

**Parameters**
<definitions>
  <definition term="field">
    Numeric expression. If `null`, the function returns `null`.
  </definition>
  <definition term="min">
    The min value to clamp data into.
  </definition>
  <definition term="max">
    The max value to clamp data into.
  </definition>
</definitions>

**Description**
Limits (or clamps) the values of all samples to have a lower limit of min and an upper limit of max.
**Supported types**

| field         | min           | max           | result        |
|---------------|---------------|---------------|---------------|
| boolean       | boolean       | boolean       | boolean       |
| date          | date          | date          | date          |
| double        | double        | double        | double        |
| double        | integer       | integer       | double        |
| double        | long          | long          | double        |
| double        | unsigned_long | unsigned_long | double        |
| integer       | double        | double        | double        |
| integer       | integer       | integer       | integer       |
| integer       | long          | long          | long          |
| integer       | unsigned_long | unsigned_long | unsigned_long |
| ip            | ip            | ip            | ip            |
| keyword       | keyword       | keyword       | keyword       |
| long          | double        | double        | double        |
| long          | integer       | integer       | long          |
| long          | long          | long          | long          |
| long          | unsigned_long | unsigned_long | unsigned_long |
| unsigned_long | double        | double        | double        |
| unsigned_long | integer       | integer       | unsigned_long |
| unsigned_long | long          | long          | long          |
| unsigned_long | unsigned_long | unsigned_long | unsigned_long |
| version       | version       | version       | version       |

**Example**
```esql
TS k8s
| EVAL full_clamped_cost = clamp(network.cost, clamp_max(network.bytes_in, 5), network.bytes_in / 100)
| KEEP full_clamped_cost, @timestamp
```


| full_clamped_cost:double | @timestamp:datetime      |
|--------------------------|--------------------------|
| 10.0                     | 2024-05-10T00:18:33.000Z |
| 9.0                      | 2024-05-10T00:04:49.000Z |
| 9.0                      | 2024-05-10T00:15:51.000Z |
| 9.0                      | 2024-05-10T00:17:12.000Z |
| 9.0                      | 2024-05-10T00:20:46.000Z |


## `CLAMP_MIN`

<applies-to>
  - Elastic Stack: Preview since 9.3
</applies-to>

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/clamp_min.svg)

**Parameters**
<definitions>
  <definition term="field">
    field to clamp.
  </definition>
  <definition term="min">
    The min value to clamp data into.
  </definition>
</definitions>

**Description**
Limits (or clamps) all input sample values to a lower bound of min. Any value below min is set to min.
**Supported types**

| field         | min           | result        |
|---------------|---------------|---------------|
| boolean       | boolean       | boolean       |
| date          | date          | date          |
| double        | double        | double        |
| double        | integer       | double        |
| double        | long          | double        |
| double        | unsigned_long | double        |
| integer       | double        | double        |
| integer       | integer       | integer       |
| integer       | long          | long          |
| integer       | unsigned_long | unsigned_long |
| ip            | ip            | ip            |
| keyword       | keyword       | keyword       |
| long          | double        | double        |
| long          | integer       | long          |
| long          | long          | long          |
| long          | unsigned_long | unsigned_long |
| unsigned_long | double        | double        |
| unsigned_long | integer       | unsigned_long |
| unsigned_long | long          | long          |
| unsigned_long | unsigned_long | unsigned_long |
| version       | version       | version       |

**Example**
```esql
FROM k8s
| STATS full_clamped_cost=sum(clamp(network.cost, 1, 2)), clamped_cost=sum(clamp_max(network.cost, 1)), clamped_min_cost=sum(clamp_min(network.cost, 10)) BY time_bucket = bucket(@timestamp,1minute)
```


| full_clamped_cost:double | clamped_cost:double | clamped_min_cost:double | time_bucket:datetime     |
|--------------------------|---------------------|-------------------------|--------------------------|
| 39.0                     | 20.0                | 206.25                  | 2024-05-10T00:09:00.000Z |
| 29.125                   | 15.5                | 173.0                   | 2024-05-10T00:18:00.000Z |
| 28.0                     | 14.125              | 155.625                 | 2024-05-10T00:17:00.000Z |
| 23.25                    | 12.0                | 124.875                 | 2024-05-10T00:08:00.000Z |


## `CLAMP_MAX`

<applies-to>
  - Elastic Stack: Preview since 9.3
</applies-to>

**Syntax**
![Embedded](https://www.elastic.co/elastic/docs-builder/docs/2649/reference/query-languages/esql/images/functions/clamp_max.svg)

**Parameters**
<definitions>
  <definition term="field">
    field to clamp.
  </definition>
  <definition term="max">
    The max value to clamp data into.
  </definition>
</definitions>

**Description**
Limits (or clamps) all input sample values to an upper bound of max. Any value above max is reduced to max.
**Supported types**

| field         | max           | result        |
|---------------|---------------|---------------|
| boolean       | boolean       | boolean       |
| date          | date          | date          |
| double        | double        | double        |
| double        | integer       | double        |
| double        | long          | double        |
| double        | unsigned_long | double        |
| integer       | double        | double        |
| integer       | integer       | integer       |
| integer       | long          | long          |
| integer       | unsigned_long | unsigned_long |
| ip            | ip            | ip            |
| keyword       | keyword       | keyword       |
| long          | double        | double        |
| long          | integer       | long          |
| long          | long          | long          |
| long          | unsigned_long | unsigned_long |
| unsigned_long | double        | double        |
| unsigned_long | integer       | unsigned_long |
| unsigned_long | long          | long          |
| unsigned_long | unsigned_long | unsigned_long |
| version       | version       | version       |

**Example**
```esql
TS k8s
| STATS full_clamped_cost=sum(clamp(network.cost, 1, 2)), clamped_cost=sum(clamp_max(network.cost, 1)), clamped_min_cost=sum(clamp_min(network.cost, 10)) BY time_bucket = bucket(@timestamp,1minute)
```


| full_clamped_cost:double | clamped_cost:double | clamped_min_cost:double | time_bucket:datetime     |
|--------------------------|---------------------|-------------------------|--------------------------|
| 18.0                     | 9.0                 | 94.875                  | 2024-05-10T00:09:00.000Z |
| 15.25                    | 8.0                 | 84.125                  | 2024-05-10T00:08:00.000Z |
| 15.0                     | 8.0                 | 83.5                    | 2024-05-10T00:15:00.000Z |
| 13.75                    | 7.0                 | 71.625                  | 2024-05-10T00:22:00.000Z |
| 13.125                   | 7.5                 | 90.5                    | 2024-05-10T00:18:00.000Z |