---
title: ES|QL CASE function
description: 
url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case
products:
  - Elasticsearch
---

# ES|QL CASE function
## Syntax

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


## Parameters

<definitions>
  <definition term="condition">
    A condition.
  </definition>
  <definition term="trueValue">
    The expression or value that’s returned when the corresponding condition is the first to evaluate to `true`. Can be a column reference or any other expression. 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`. Both the conditions and the returned values can be any expression, including column references.  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 <applies-to>Elastic Stack: Preview in 9.3</applies-to> | exponential_histogram <applies-to>Elastic Stack: Preview in 9.3</applies-to> | exponential_histogram   |
| boolean   | exponential_histogram <applies-to>Elastic Stack: Preview in 9.3</applies-to> |                                                                              | 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 <applies-to>Elastic Stack: Preview in 9.3</applies-to>             | histogram <applies-to>Elastic Stack: Preview in 9.3</applies-to>             | histogram               |
| boolean   | histogram <applies-to>Elastic Stack: Preview in 9.3</applies-to>             |                                                                              | 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 <applies-to>Elastic Stack: Preview in 9.3</applies-to>               | tdigest <applies-to>Elastic Stack: Preview in 9.3</applies-to>               | tdigest                 |
| boolean   | tdigest <applies-to>Elastic Stack: Preview in 9.3</applies-to>               |                                                                              | 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 |

Extract error messages and count distinct ones using a column expression:
```esql
FROM sample_data
| EVAL error_message = CASE(message LIKE "*error*", message, null)
| STATS distinct_error_messages = COUNT_DISTINCT(error_message)
```


| distinct_error_messages:long |
|------------------------------|
| 1                            |