ES|QL functions and operators
- ES|QL functions and operators

ES|QL functions and operators

ES|QL provides a comprehensive set of functions and operators for working with data. The reference documentation is divided into the following categories:

Functions overview

Aggregate functions

AVG
COUNT
COUNT_DISTINCT
MAX
MEDIAN
MEDIAN_ABSOLUTE_DEVIATION
MIN
PERCENTILE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. ST_CENTROID_AGG
SUM
TOP
VALUES
WEIGHTED_AVG

Grouping functions

BUCKET

Conditional functions and expressions

Date and time functions

IP functions

Math functions

Search functions

[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. MATCH
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. QSTR

Spatial functions

String functions

Type conversion functions

TO_BOOLEAN
TO_CARTESIANPOINT
TO_CARTESIANSHAPE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_DATEPERIOD
TO_DATETIME
TO_DEGREES
TO_DOUBLE
TO_GEOPOINT
TO_GEOSHAPE
TO_INTEGER
TO_IP
TO_LONG
TO_RADIANS
TO_STRING
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_TIMEDURATION
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_UNSIGNED_LONG
TO_VERSION

Multi value functions

Operators overview

Operators

Binary operators
Unary operators
Logical operators
IS NULL and IS NOT NULL predicates
Cast (::)
IN
LIKE
RLIKE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Search operators

ES|QL aggregate functions

The STATS command supports these aggregate functions:

AVG
COUNT
COUNT_DISTINCT
MAX
MEDIAN
MEDIAN_ABSOLUTE_DEVIATION
MIN
PERCENTILE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. ST_CENTROID_AGG
SUM
TOP
VALUES
WEIGHTED_AVG

`AVG`

Syntax

Parameters

number

Description

The average of a numeric field.

Supported types

number	result
double	double
integer	double
long	double

Examples

FROM employees
| STATS AVG(height)

AVG(height):double
1.7682

The expression can use inline functions. For example, to calculate the average over a multivalued column, first use MV_AVG to average the multiple values per row, and use the result with the AVG function

FROM employees
| STATS avg_salary_change = ROUND(AVG(MV_AVG(salary_change)), 10)

avg_salary_change:double
1.3904535865

`COUNT`

Syntax

Parameters

field

Expression that outputs values to be counted. If omitted, equivalent to COUNT(*) (the number of rows).

Description

Returns the total number (count) of input values.

Supported types

field	result
boolean	long
cartesian_point	long
date	long
double	long
geo_point	long
integer	long
ip	long
keyword	long
long	long
text	long
unsigned_long	long
version	long

Examples

FROM employees
| STATS COUNT(height)

COUNT(height):long
100

To count the number of rows, use COUNT() or COUNT(*)

FROM employees
| STATS count = COUNT(*) BY languages
| SORT languages DESC

count:long	languages:integer
10	null
21	5
18	4
17	3
19	2
15	1

The expression can use inline functions. This example splits a string into multiple values using the SPLIT function and counts the values

ROW words="foo;bar;baz;qux;quux;foo"
| STATS word_count = COUNT(SPLIT(words, ";"))

word_count:long
6

To count the number of times an expression returns TRUE use a WHERE command to remove rows that shouldn’t be included

ROW n=1
| WHERE n < 0
| STATS COUNT(n)

COUNT(n):long
0

To count the same stream of data based on two different expressions use the pattern COUNT(<expression> OR NULL). This builds on the three-valued logic (3VL) of the language: TRUE OR NULL is TRUE, but FALSE OR NULL is NULL, plus the way COUNT handles NULL`s: `COUNT(TRUE) and COUNT(FALSE) are both 1, but COUNT(NULL) is 0.

ROW n=1
| STATS COUNT(n > 0 OR NULL), COUNT(n < 0 OR NULL)

COUNT(n > 0 OR NULL):long	COUNT(n < 0 OR NULL):long
1	0

`COUNT_DISTINCT`

Syntax

Parameters

field

Column or literal for which to count the number of distinct values.

precision

Precision threshold. Refer to Counts are approximate. The maximum supported value is 40000. Thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.

Description

Returns the approximate number of distinct values.

Supported types

field	precision	result
boolean	integer	long
boolean	long	long
boolean	unsigned_long	long
boolean		long
date	integer	long
date	long	long
date	unsigned_long	long
date		long
date_nanos	integer	long
date_nanos	long	long
date_nanos	unsigned_long	long
date_nanos		long
double	integer	long
double	long	long
double	unsigned_long	long
double		long
integer	integer	long
integer	long	long
integer	unsigned_long	long
integer		long
ip	integer	long
ip	long	long
ip	unsigned_long	long
ip		long
keyword	integer	long
keyword	long	long
keyword	unsigned_long	long
keyword		long
long	integer	long
long	long	long
long	unsigned_long	long
long		long
text	integer	long
text	long	long
text	unsigned_long	long
text		long
version	integer	long
version	long	long
version	unsigned_long	long
version		long

Examples

FROM hosts
| STATS COUNT_DISTINCT(ip0), COUNT_DISTINCT(ip1)

COUNT_DISTINCT(ip0):long	COUNT_DISTINCT(ip1):long
7	8

With the optional second parameter to configure the precision threshold

FROM hosts
| STATS COUNT_DISTINCT(ip0, 80000), COUNT_DISTINCT(ip1, 5)

COUNT_DISTINCT(ip0, 80000):long	COUNT_DISTINCT(ip1, 5):long
7	9

The expression can use inline functions. This example splits a string into multiple values using the SPLIT function and counts the unique values

ROW words="foo;bar;baz;qux;quux;foo"
| STATS distinct_word_count = COUNT_DISTINCT(SPLIT(words, ";"))

distinct_word_count:long
5

Counts are approximate

Computing exact counts requires loading values into a set and returning its size. This doesn’t scale when working on high-cardinality sets and/or large values as the required memory usage and the need to communicate those per-shard sets between nodes would utilize too many resources of the cluster.

This COUNT_DISTINCT function is based on the HyperLogLog++ algorithm, which counts based on the hashes of the values with some interesting properties:

configurable precision, which decides on how to trade memory for accuracy,
excellent accuracy on low-cardinality sets,
fixed memory usage: no matter if there are tens or billions of unique values, memory usage only depends on the configured precision.

For a precision threshold of c, the implementation that we are using requires about c * 8 bytes.

The following chart shows how the error varies before and after the threshold:

For all 3 thresholds, counts have been accurate up to the configured threshold. Although not guaranteed, this is likely to be the case. Accuracy in practice depends on the dataset in question. In general, most datasets show consistently good accuracy. Also note that even with a threshold as low as 100, the error remains very low (1-6% as seen in the above graph) even when counting millions of items.

The HyperLogLog++ algorithm depends on the leading zeros of hashed values, the exact distributions of hashes in a dataset can affect the accuracy of the cardinality.

The COUNT_DISTINCT function takes an optional second parameter to configure the precision threshold. The precision_threshold options allows to trade memory for accuracy, and defines a unique count below which counts are expected to be close to accurate. Above this value, counts might become a bit more fuzzy. The maximum supported value is 40000, thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.

`MAX`

Syntax

Parameters

field

Description

The maximum value of a field.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
version	version

Examples

FROM employees
| STATS MAX(languages)

MAX(languages):integer
5

The expression can use inline functions. For example, to calculate the maximum over an average of a multivalued column, use MV_AVG to first average the multiple values per row, and use the result with the MAX function

FROM employees
| STATS max_avg_salary_change = MAX(MV_AVG(salary_change))

max_avg_salary_change:double
13.75

`MEDIAN`

Syntax

Parameters

number

Description

The value that is greater than half of all values and less than half of all values, also known as the 50% PERCENTILE.

Like PERCENTILE, MEDIAN is usually approximate.

Supported types

number	result
double	double
integer	double
long	double

Examples

FROM employees
| STATS MEDIAN(salary), PERCENTILE(salary, 50)

MEDIAN(salary):double	PERCENTILE(salary, 50):double
47003	47003

The expression can use inline functions. For example, to calculate the median of the maximum values of a multivalued column, first use MV_MAX to get the maximum value per row, and use the result with the MEDIAN function

FROM employees
| STATS median_max_salary_change = MEDIAN(MV_MAX(salary_change))

median_max_salary_change:double
7.69

MEDIAN is also non-deterministic. This means you can get slightly different results using the same data.

`MEDIAN_ABSOLUTE_DEVIATION`

Syntax

Parameters

number

Description

Returns the median absolute deviation, a measure of variability. It is a robust statistic, meaning that it is useful for describing data that may have outliers, or may not be normally distributed. For such data it can be more descriptive than standard deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X, the median absolute deviation is median(|median(X) - X|).

Like PERCENTILE, MEDIAN_ABSOLUTE_DEVIATION is usually approximate.

Supported types

number	result
double	double
integer	double
long	double

Examples

FROM employees
| STATS MEDIAN(salary), MEDIAN_ABSOLUTE_DEVIATION(salary)

MEDIAN(salary):double	MEDIAN_ABSOLUTE_DEVIATION(salary):double
47003	10096.5

The expression can use inline functions. For example, to calculate the the median absolute deviation of the maximum values of a multivalued column, first use MV_MAX to get the maximum value per row, and use the result with the MEDIAN_ABSOLUTE_DEVIATION function

FROM employees
| STATS m_a_d_max_salary_change = MEDIAN_ABSOLUTE_DEVIATION(MV_MAX(salary_change))

m_a_d_max_salary_change:double
5.69

MEDIAN_ABSOLUTE_DEVIATION is also non-deterministic. This means you can get slightly different results using the same data.

`MIN`

Syntax

Parameters

field

Description

The minimum value of a field.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
version	version

Examples

FROM employees
| STATS MIN(languages)

MIN(languages):integer
1

The expression can use inline functions. For example, to calculate the minimum over an average of a multivalued column, use MV_AVG to first average the multiple values per row, and use the result with the MIN function

FROM employees
| STATS min_avg_salary_change = MIN(MV_AVG(salary_change))

min_avg_salary_change:double
-8.46

`PERCENTILE`

Syntax

Parameters

number

percentile

Description

Returns the value at which a certain percentage of observed values occur. For example, the 95th percentile is the value which is greater than 95% of the observed values and the 50th percentile is the MEDIAN.

Supported types

number	percentile	result
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	double
integer	long	double
long	double	double
long	integer	double
long	long	double

Examples

FROM employees
| STATS p0 = PERCENTILE(salary,  0)
     , p50 = PERCENTILE(salary, 50)
     , p99 = PERCENTILE(salary, 99)

p0:double	p50:double	p99:double
25324	47003	74970.29

The expression can use inline functions. For example, to calculate a percentile of the maximum values of a multivalued column, first use MV_MAX to get the maximum value per row, and use the result with the PERCENTILE function

FROM employees
| STATS p80_max_salary_change = PERCENTILE(MV_MAX(salary_change), 80)

p80_max_salary_change:double
12.132

`PERCENTILE` is (usually) approximate

There are many different algorithms to calculate percentiles. The naive implementation simply stores all the values in a sorted array. To find the 50th percentile, you simply find the value that is at my_array[count(my_array) * 0.5].

Clearly, the naive implementation does not scale — the sorted array grows linearly with the number of values in your dataset. To calculate percentiles across potentially billions of values in an Elasticsearch cluster, approximate percentiles are calculated.

The algorithm used by the percentile metric is called TDigest (introduced by Ted Dunning in Computing Accurate Quantiles using T-Digests).

When using this metric, there are a few guidelines to keep in mind:

Accuracy is proportional to q(1-q). This means that extreme percentiles (e.g. 99%) are more accurate than less extreme percentiles, such as the median
For small sets of values, percentiles are highly accurate (and potentially 100% accurate if the data is small enough).
As the quantity of values in a bucket grows, the algorithm begins to approximate the percentiles. It is effectively trading accuracy for memory savings. The exact level of inaccuracy is difficult to generalize, since it depends on your data distribution and volume of data being aggregated

The following chart shows the relative error on a uniform distribution depending on the number of collected values and the requested percentile:

It shows how precision is better for extreme percentiles. The reason why error diminishes for large number of values is that the law of large numbers makes the distribution of values more and more uniform and the t-digest tree can do a better job at summarizing it. It would not be the case on more skewed distributions.

PERCENTILE is also non-deterministic. This means you can get slightly different results using the same data.

`ST_CENTROID_AGG`

Syntax

Parameters

field

Description

Calculate the spatial centroid over a field with spatial point geometry type.

Supported types

field	result
cartesian_point	cartesian_point
geo_point	geo_point

Example

FROM airports
| STATS centroid=ST_CENTROID_AGG(location)

centroid:geo_point
POINT(-0.030548143003023033 24.37553649504829)

`SUM`

Syntax

Parameters

number

Description

The sum of a numeric expression.

Supported types

number	result
double	double
integer	long
long	long

Examples

FROM employees
| STATS SUM(languages)

SUM(languages):long
281

The expression can use inline functions. For example, to calculate the sum of each employee’s maximum salary changes, apply the MV_MAX function to each row and then sum the results

FROM employees
| STATS total_salary_changes = SUM(MV_MAX(salary_change))

total_salary_changes:double
446.75

`TOP`

Syntax

Parameters

field

The field to collect the top values for.

limit

The maximum number of values to collect.

order

The order to calculate the top values. Either asc or desc.

Description

Collects the top values for a field. Includes repeated values.

Supported types

field	limit	order	result
boolean	integer	keyword	boolean
date	integer	keyword	date
double	integer	keyword	double
integer	integer	keyword	integer
ip	integer	keyword	ip
keyword	integer	keyword	keyword
long	integer	keyword	long
text	integer	keyword	keyword

Example

FROM employees
| STATS top_salaries = TOP(salary, 3, "desc"), top_salary = MAX(salary)

top_salaries:integer	top_salary:integer
[74999, 74970, 74572]	74999

`VALUES`

Do not use VALUES on production environments. This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

Syntax

Parameters

field

Description

Returns all values in a group as a multivalued field. The order of the returned values isn’t guaranteed. If you need the values returned in order use MV_SORT.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
version	version

Example

  FROM employees
| EVAL first_letter = SUBSTRING(first_name, 0, 1)
| STATS first_name=MV_SORT(VALUES(first_name)) BY first_letter
| SORT first_letter

first_name:keyword	first_letter:keyword
[Alejandro, Amabile, Anneke, Anoosh, Arumugam]	A
[Basil, Berhard, Berni, Bezalel, Bojan, Breannda, Brendon]	B
[Charlene, Chirstian, Claudi, Cristinel]	C
[Danel, Divier, Domenick, Duangkaew]	D
[Ebbe, Eberhardt, Erez]	E
Florian	F
[Gao, Georgi, Georgy, Gino, Guoxiang]	G
[Heping, Hidefumi, Hilari, Hironobu, Hironoby, Hisao]	H
[Jayson, Jungsoon]	J
[Kazuhide, Kazuhito, Kendra, Kenroku, Kshitij, Kwee, Kyoichi]	K
[Lillian, Lucien]	L
[Magy, Margareta, Mary, Mayuko, Mayumi, Mingsen, Mokhtar, Mona, Moss]	M
Otmar	O
[Parto, Parviz, Patricio, Prasadram, Premal]	P
[Ramzi, Remzi, Reuven]	R
[Sailaja, Saniya, Sanjiv, Satosi, Shahaf, Shir, Somnath, Sreekrishna, Sudharsan, Sumant, Suzette]	S
[Tse, Tuval, Tzvetan]	T
[Udi, Uri]	U
[Valdiodio, Valter, Vishv]	V
Weiyi	W
Xinglin	X
[Yinghua, Yishay, Yongqiao]	Y
[Zhongwei, Zvonko]	Z
null	null

This can use a significant amount of memory and ES|QL doesn’t yet grow aggregations beyond memory. So this aggregation will work until it is used to collect more values than can fit into memory. Once it collects too many values it will fail the query with a Circuit Breaker Error.

`WEIGHTED_AVG`

Syntax

Parameters

number

A numeric value.

weight

A numeric weight.

Description

The weighted average of a numeric expression.

Supported types

number	weight	result
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	double
integer	long	double
long	double	double
long	integer	double
long	long	double

Example

FROM employees
| STATS w_avg = WEIGHTED_AVG(salary, height) by languages
| EVAL w_avg = ROUND(w_avg)
| KEEP w_avg, languages
| SORT languages

w_avg:double	languages:integer
51464.0	1
48477.0	2
52379.0	3
47990.0	4
42119.0	5
52142.0	null

ES|QL grouping functions

The STATS command supports these grouping functions:

BUCKET

`BUCKET`

Syntax

Parameters

field

Numeric or date expression from which to derive buckets.

buckets

Target number of buckets, or desired bucket size if from and to parameters are omitted.

from

Start of the range. Can be a number, a date or a date expressed as a string.

to

End of the range. Can be a number, a date or a date expressed as a string.

Description

Creates groups of values - buckets - out of a datetime or numeric input. The size of the buckets can either be provided directly, or chosen based on a recommended count and values range.

Supported types

field	buckets	from	to	result
date	date_period			date
date	integer	date	date	date
date	integer	date	keyword	date
date	integer	date	text	date
date	integer	keyword	date	date
date	integer	keyword	keyword	date
date	integer	keyword	text	date
date	integer	text	date	date
date	integer	text	keyword	date
date	integer	text	text	date
date	time_duration			date
double	double			double
double	integer	double	double	double
double	integer	double	integer	double
double	integer	double	long	double
double	integer	integer	double	double
double	integer	integer	integer	double
double	integer	integer	long	double
double	integer	long	double	double
double	integer	long	integer	double
double	integer	long	long	double
double	integer			double
double	long			double
integer	double			double
integer	integer	double	double	double
integer	integer	double	integer	double
integer	integer	double	long	double
integer	integer	integer	double	double
integer	integer	integer	integer	double
integer	integer	integer	long	double
integer	integer	long	double	double
integer	integer	long	integer	double
integer	integer	long	long	double
integer	integer			double
integer	long			double
long	double			double
long	integer	double	double	double
long	integer	double	integer	double
long	integer	double	long	double
long	integer	integer	double	double
long	integer	integer	integer	double
long	integer	integer	long	double
long	integer	long	double	double
long	integer	long	integer	double
long	integer	long	long	double
long	integer			double
long	long			double

Examples

BUCKET can work in two modes: one in which the size of the bucket is computed based on a buckets count recommendation (four parameters) and a range, and another in which the bucket size is provided directly (two parameters).

Using a target number of buckets, a start of a range, and an end of a range, BUCKET picks an appropriate bucket size to generate the target number of buckets or fewer. For example, asking for at most 20 buckets over a year results in monthly buckets:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS hire_date = MV_SORT(VALUES(hire_date)) BY month = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| SORT hire_date

hire_date:date	month:date
[1985-02-18T00:00:00.000Z, 1985-02-24T00:00:00.000Z]	1985-02-01T00:00:00.000Z
1985-05-13T00:00:00.000Z	1985-05-01T00:00:00.000Z
1985-07-09T00:00:00.000Z	1985-07-01T00:00:00.000Z
1985-09-17T00:00:00.000Z	1985-09-01T00:00:00.000Z
[1985-10-14T00:00:00.000Z, 1985-10-20T00:00:00.000Z]	1985-10-01T00:00:00.000Z
[1985-11-19T00:00:00.000Z, 1985-11-20T00:00:00.000Z, 1985-11-21T00:00:00.000Z]	1985-11-01T00:00:00.000Z

The goal isn’t to provide exactly the target number of buckets, it’s to pick a range that people are comfortable with that provides at most the target number of buckets.

Combine BUCKET with an aggregation to create a histogram:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS hires_per_month = COUNT(*) BY month = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| SORT month

hires_per_month:long	month:date
2	1985-02-01T00:00:00.000Z
1	1985-05-01T00:00:00.000Z
1	1985-07-01T00:00:00.000Z
1	1985-09-01T00:00:00.000Z
2	1985-10-01T00:00:00.000Z
4	1985-11-01T00:00:00.000Z

BUCKET does not create buckets that don’t match any documents. That’s why this example is missing 1985-03-01 and other dates.

Asking for more buckets can result in a smaller range. For example, asking for at most 100 buckets in a year results in weekly buckets:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, 100, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| SORT week

hires_per_week:long	week:date
2	1985-02-18T00:00:00.000Z
1	1985-05-13T00:00:00.000Z
1	1985-07-08T00:00:00.000Z
1	1985-09-16T00:00:00.000Z
2	1985-10-14T00:00:00.000Z
4	1985-11-18T00:00:00.000Z

BUCKET does not filter any rows. It only uses the provided range to pick a good bucket size. For rows with a value outside of the range, it returns a bucket value that corresponds to a bucket outside the range. Combine`BUCKET` with WHERE to filter rows.

If the desired bucket size is known in advance, simply provide it as the second argument, leaving the range out:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, 1 week)
| SORT week

hires_per_week:long	week:date
2	1985-02-18T00:00:00.000Z
1	1985-05-13T00:00:00.000Z
1	1985-07-08T00:00:00.000Z
1	1985-09-16T00:00:00.000Z
2	1985-10-14T00:00:00.000Z
4	1985-11-18T00:00:00.000Z

When providing the bucket size as the second parameter, it must be a time duration or date period.

BUCKET can also operate on numeric fields. For example, to create a salary histogram:

FROM employees
| STATS COUNT(*) by bs = BUCKET(salary, 20, 25324, 74999)
| SORT bs

COUNT(*):long	bs:double
9	25000.0
9	30000.0
18	35000.0
11	40000.0
11	45000.0
10	50000.0
7	55000.0
9	60000.0
8	65000.0
8	70000.0

Unlike the earlier example that intentionally filters on a date range, you rarely want to filter on a numeric range. You have to find the min and max separately. ES|QL doesn’t yet have an easy way to do that automatically.

The range can be omitted if the desired bucket size is known in advance. Simply provide it as the second argument:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS c = COUNT(1) BY b = BUCKET(salary, 5000.)
| SORT b

c:long	b:double
1	25000.0
1	30000.0
1	40000.0
2	45000.0
2	50000.0
1	55000.0
1	60000.0
1	65000.0
1	70000.0

Create hourly buckets for the last 24 hours, and calculate the number of events per hour:

FROM sample_data
| WHERE @timestamp >= NOW() - 1 day and @timestamp < NOW()
| STATS COUNT(*) BY bucket = BUCKET(@timestamp, 25, NOW() - 1 day, NOW())

COUNT(*):long	bucket:date

Create monthly buckets for the year 1985, and calculate the average salary by hiring month

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS AVG(salary) BY bucket = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| SORT bucket

AVG(salary):double	bucket:date
46305.0	1985-02-01T00:00:00.000Z
44817.0	1985-05-01T00:00:00.000Z
62405.0	1985-07-01T00:00:00.000Z
49095.0	1985-09-01T00:00:00.000Z
51532.0	1985-10-01T00:00:00.000Z
54539.75	1985-11-01T00:00:00.000Z

BUCKET may be used in both the aggregating and grouping part of the STATS … BY … command provided that in the aggregating part the function is referenced by an alias defined in the grouping part, or that it is invoked with the exact same expression:

FROM employees
| STATS s1 = b1 + 1, s2 = BUCKET(salary / 1000 + 999, 50.) + 2 BY b1 = BUCKET(salary / 100 + 99, 50.), b2 = BUCKET(salary / 1000 + 999, 50.)
| SORT b1, b2
| KEEP s1, b1, s2, b2

s1:double	b1:double	s2:double	b2:double
351.0	350.0	1002.0	1000.0
401.0	400.0	1002.0	1000.0
451.0	450.0	1002.0	1000.0
501.0	500.0	1002.0	1000.0
551.0	550.0	1002.0	1000.0
601.0	600.0	1002.0	1000.0
601.0	600.0	1052.0	1050.0
651.0	650.0	1052.0	1050.0
701.0	700.0	1052.0	1050.0
751.0	750.0	1052.0	1050.0
801.0	800.0	1052.0	1050.0

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`

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

condition	trueValue	elseValue	result
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	double	double	double
boolean	double		double
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	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	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:

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:

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:

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

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

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
geo_point	geo_point	geo_point
geo_shape	geo_shape	geo_shape
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

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

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

`GREATEST`

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.

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.

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

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

a:integer	b:integer	g:integer
10	20	20

`LEAST`

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

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

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

a:integer	b:integer	l:integer
10	20	10

ES|QL date-time functions

ES|QL supports these date-time functions:

`DATE_DIFF`

Syntax

Parameters

unit

Time difference unit

startTimestamp

A string representing a start timestamp

endTimestamp

A string representing an end timestamp

Description

Subtracts the startTimestamp from the endTimestamp and returns the difference in multiples of unit. If startTimestamp is later than the endTimestamp, negative values are returned.

Datetime difference units
unit	abbreviations
year	years, yy, yyyy
quarter	quarters, qq, q
month	months, mm, m
dayofyear	dy, y
day	days, dd, d
week	weeks, wk, ww
weekday	weekdays, dw
hour	hours, hh
minute	minutes, mi, n
second	seconds, ss, s
millisecond	milliseconds, ms
microsecond	microseconds, mcs
nanosecond	nanoseconds, ns

Note that while there is an overlap between the function’s supported units and ES|QL’s supported time span literals, these sets are distinct and not interchangeable. Similarly, the supported abbreviations are conveniently shared with implementations of this function in other established products and not necessarily common with the date-time nomenclature used by Elasticsearch.

Supported types

unit	startTimestamp	endTimestamp	result
keyword	date	date	integer
text	date	date	integer

Examples

ROW date1 = TO_DATETIME("2023-12-02T11:00:00.000Z"), date2 = TO_DATETIME("2023-12-02T11:00:00.001Z")
| EVAL dd_ms = DATE_DIFF("microseconds", date1, date2)

date1:date	date2:date	dd_ms:integer
2023-12-02T11:00:00.000Z	2023-12-02T11:00:00.001Z	1000

When subtracting in calendar units - like year, month a.s.o. - only the fully elapsed units are counted. To avoid this and obtain also remainders, simply switch to the next smaller unit and do the date math accordingly.

ROW end_23=TO_DATETIME("2023-12-31T23:59:59.999Z"),
  start_24=TO_DATETIME("2024-01-01T00:00:00.000Z"),
    end_24=TO_DATETIME("2024-12-31T23:59:59.999")
| EVAL end23_to_start24=DATE_DIFF("year", end_23, start_24)
| EVAL end23_to_end24=DATE_DIFF("year", end_23, end_24)
| EVAL start_to_end_24=DATE_DIFF("year", start_24, end_24)

end_23:date	start_24:date	end_24:date	end23_to_start24:integer	end23_to_end24:integer	start_to_end_24:integer
2023-12-31T23:59:59.999Z	2024-01-01T00:00:00.000Z	2024-12-31T23:59:59.999Z	0	1	0

`DATE_EXTRACT`

Syntax

Parameters

datePart

Part of the date to extract. Can be: aligned_day_of_week_in_month, aligned_day_of_week_in_year, aligned_week_of_month, aligned_week_of_year, ampm_of_day, clock_hour_of_ampm, clock_hour_of_day, day_of_month, day_of_week, day_of_year, epoch_day, era, hour_of_ampm, hour_of_day, instant_seconds, micro_of_day, micro_of_second, milli_of_day, milli_of_second, minute_of_day, minute_of_hour, month_of_year, nano_of_day, nano_of_second, offset_seconds, proleptic_month, second_of_day, second_of_minute, year, or year_of_era. Refer to java.time.temporal.ChronoField for a description of these values. If null, the function returns null.

date

Date expression. If null, the function returns null.

Description

Extracts parts of a date, like year, month, day, hour.

Supported types

datePart	date	result
keyword	date	long
text	date	long

Examples

ROW date = DATE_PARSE("yyyy-MM-dd", "2022-05-06")
| EVAL year = DATE_EXTRACT("year", date)

date:date	year:long
2022-05-06T00:00:00.000Z	2022

Find all events that occurred outside of business hours (before 9 AM or after 5PM), on any given date:

FROM sample_data
| WHERE DATE_EXTRACT("hour_of_day", @timestamp) < 9 AND DATE_EXTRACT("hour_of_day", @timestamp) >= 17

@timestamp:date	client_ip:ip	event_duration:long	message:keyword

`DATE_FORMAT`

Syntax

Parameters

dateFormat

Date format (optional). If no format is specified, the yyyy-MM-dd'T'HH:mm:ss.SSSZ format is used. If null, the function returns null.

date

Date expression. If null, the function returns null.

Description

Returns a string representation of a date, in the provided format.

Supported types

dateFormat	date	result
keyword	date	keyword
text	date	keyword

Example

FROM employees
| KEEP first_name, last_name, hire_date
| EVAL hired = DATE_FORMAT("yyyy-MM-dd", hire_date)

first_name:keyword	last_name:keyword	hire_date:date	hired:keyword
Alejandro	McAlpine	1991-06-26T00:00:00.000Z	1991-06-26
Amabile	Gomatam	1992-11-18T00:00:00.000Z	1992-11-18
Anneke	Preusig	1989-06-02T00:00:00.000Z	1989-06-02

`DATE_PARSE`

Syntax

Parameters

datePattern

The date format. Refer to the DateTimeFormatter documentation for the syntax. If null, the function returns null.

dateString

Date expression as a string. If null or an empty string, the function returns null.

Description

Returns a date by parsing the second argument using the format specified in the first argument.

Supported types

datePattern	dateString	result
keyword	keyword	date
keyword	text	date
text	keyword	date
text	text	date

Example

ROW date_string = "2022-05-06"
| EVAL date = DATE_PARSE("yyyy-MM-dd", date_string)

date_string:keyword	date:date
2022-05-06	2022-05-06T00:00:00.000Z

`DATE_TRUNC`

Syntax

Parameters

interval

Interval; expressed using the timespan literal syntax.

date

Date expression

Description

Rounds down a date to the closest interval.

Supported types

interval	date	result
date_period	date	date
date_period	date_nanos	date_nanos
time_duration	date	date
time_duration	date_nanos	date_nanos

Examples

FROM employees
| KEEP first_name, last_name, hire_date
| EVAL year_hired = DATE_TRUNC(1 year, hire_date)

first_name:keyword	last_name:keyword	hire_date:date	year_hired:date
Alejandro	McAlpine	1991-06-26T00:00:00.000Z	1991-01-01T00:00:00.000Z
Amabile	Gomatam	1992-11-18T00:00:00.000Z	1992-01-01T00:00:00.000Z
Anneke	Preusig	1989-06-02T00:00:00.000Z	1989-01-01T00:00:00.000Z

Combine DATE_TRUNC with STATS to create date histograms. For example, the number of hires per year:

FROM employees
| EVAL year = DATE_TRUNC(1 year, hire_date)
| STATS hires = COUNT(emp_no) BY year
| SORT year

hires:long	year:date
11	1985-01-01T00:00:00.000Z
11	1986-01-01T00:00:00.000Z
15	1987-01-01T00:00:00.000Z
9	1988-01-01T00:00:00.000Z
13	1989-01-01T00:00:00.000Z
12	1990-01-01T00:00:00.000Z
6	1991-01-01T00:00:00.000Z
8	1992-01-01T00:00:00.000Z
3	1993-01-01T00:00:00.000Z
4	1994-01-01T00:00:00.000Z
5	1995-01-01T00:00:00.000Z
1	1996-01-01T00:00:00.000Z
1	1997-01-01T00:00:00.000Z
1	1999-01-01T00:00:00.000Z

Or an hourly error rate:

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

`NOW`

Syntax

Parameters

Description

Returns current date and time.

Supported types

result
date

Examples

ROW current_date = NOW()

y:keyword
20

To retrieve logs from the last hour:

FROM sample_data
| WHERE @timestamp > NOW() - 1 hour

@timestamp:date	client_ip:ip	event_duration:long	message:keyword

ES|QL IP functions

ES|QL supports these IP functions:

`CIDR_MATCH`

Syntax

Parameters

ip

IP address of type ip (both IPv4 and IPv6 are supported).

blockX

CIDR block to test the IP against.

Description

Returns true if the provided IP is contained in one of the provided CIDR blocks.

Supported types

ip	blockX	result
ip	keyword	boolean
ip	text	boolean

Example

FROM hosts
| WHERE CIDR_MATCH(ip1, "127.0.0.2/32", "127.0.0.3/32")
| KEEP card, host, ip0, ip1

card:keyword	host:keyword	ip0:ip	ip1:ip
eth1	beta	127.0.0.1	127.0.0.2
eth0	gamma	fe80::cae2:65ff:fece:feb9	127.0.0.3

`IP_PREFIX`

Syntax

Parameters

ip

IP address of type ip (both IPv4 and IPv6 are supported).

prefixLengthV4

Prefix length for IPv4 addresses.

prefixLengthV6

Prefix length for IPv6 addresses.

Description

Truncates an IP to a given prefix length.

Supported types

ip	prefixLengthV4	prefixLengthV6	result
ip	integer	integer	ip

Example

row ip4 = to_ip("1.2.3.4"), ip6 = to_ip("fe80::cae2:65ff:fece:feb9")
| eval ip4_prefix = ip_prefix(ip4, 24, 0), ip6_prefix = ip_prefix(ip6, 0, 112);

ip4:ip	ip6:ip	ip4_prefix:ip	ip6_prefix:ip
1.2.3.4	fe80::cae2:65ff:fece:feb9	1.2.3.0	fe80::cae2:65ff:fece:0000

ES|QL mathematical functions

ES|QL supports these mathematical functions:

`ABS`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the absolute value.

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Examples

ROW number = -1.0
| EVAL abs_number = ABS(number)

number:double	abs_number:double
-1.0	1.0

FROM employees
| KEEP first_name, last_name, height
| EVAL abs_height = ABS(0.0 - height)

first_name:keyword	last_name:keyword	height:double	abs_height:double
Alejandro	McAlpine	1.48	1.48
Amabile	Gomatam	2.09	2.09
Anneke	Preusig	1.56	1.56

`ACOS`

Syntax

Parameters

number

Number between -1 and 1. If null, the function returns null.

Description

Returns the arccosine of n as an angle, expressed in radians.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=.9
| EVAL acos=ACOS(a)

a:double	acos:double
.9	0.45102681179626236

`ASIN`

Syntax

Parameters

number

Number between -1 and 1. If null, the function returns null.

Description

Returns the arcsine of the input numeric expression as an angle, expressed in radians.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=.9
| EVAL asin=ASIN(a)

a:double	asin:double
.9	1.1197695149986342

`ATAN`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the arctangent of the input numeric expression as an angle, expressed in radians.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=12.9
| EVAL atan=ATAN(a)

a:double	atan:double
12.9	1.4934316673669235

`ATAN2`

Syntax

Parameters

y_coordinate

y coordinate. If null, the function returns null.

x_coordinate

x coordinate. If null, the function returns null.

Description

The angle between the positive x-axis and the ray from the origin to the point (x , y) in the Cartesian plane, expressed in radians.

Supported types

y_coordinate	x_coordinate	result
double	double	double
double	integer	double
double	long	double
double	unsigned_long	double
integer	double	double
integer	integer	double
integer	long	double
integer	unsigned_long	double
long	double	double
long	integer	double
long	long	double
long	unsigned_long	double
unsigned_long	double	double
unsigned_long	integer	double
unsigned_long	long	double
unsigned_long	unsigned_long	double

Example

ROW y=12.9, x=.6
| EVAL atan2=ATAN2(y, x)

y:double	x:double	atan2:double
12.9	0.6	1.5243181954438936

`CBRT`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the cube root of a number. The input can be any numeric value, the return value is always a double. Cube roots of infinities are null.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW d = 1000.0
| EVAL c = cbrt(d)

d: double	c:double
1000.0	10.0

`CEIL`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Round a number up to the nearest integer.

This is a noop for long (including unsigned) and integer. For double this picks the closest double value to the integer similar to Math.ceil).

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Example

ROW a=1.8
| EVAL a=CEIL(a)

a:double
2

`COS`

Syntax

Parameters

angle

An angle, in radians. If null, the function returns null.

Description

Returns the cosine of an angle.

Supported types

angle	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL cos=COS(a)

a:double	cos:double
1.8	-0.2272020946930871

`COSH`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the hyperbolic cosine of a number.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL cosh=COSH(a)

a:double	cosh:double
1.8	3.1074731763172667

`E`

Syntax

Parameters

Description

Returns Euler’s number).

Supported types

result
double

Example

ROW E()

E():double
2.718281828459045

`EXP`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the value of e raised to the power of the given number.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW d = 5.0
| EVAL s = EXP(d)

d: double	s:double
5.0	148.413159102576603

`FLOOR`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Round a number down to the nearest integer.

This is a noop for long (including unsigned) and integer. For double this picks the closest double value to the integer similar to Math.floor).

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Example

ROW a=1.8
| EVAL a=FLOOR(a)

a:double
1

`HYPOT`

Syntax

Parameters

number1

Numeric expression. If null, the function returns null.

number2

Numeric expression. If null, the function returns null.

Description

Returns the hypotenuse of two numbers. The input can be any numeric values, the return value is always a double. Hypotenuses of infinities are null.

Supported types

number1	number2	result
double	double	double
double	integer	double
double	long	double
double	unsigned_long	double
integer	double	double
integer	integer	double
integer	long	double
integer	unsigned_long	double
long	double	double
long	integer	double
long	long	double
long	unsigned_long	double
unsigned_long	double	double
unsigned_long	integer	double
unsigned_long	long	double
unsigned_long	unsigned_long	double

Example

ROW a = 3.0, b = 4.0
| EVAL c = HYPOT(a, b)

a:double	b:double	c:double
3.0	4.0	5.0

`LOG`

Syntax

Parameters

base

Base of logarithm. If null, the function returns null. If not provided, this function returns the natural logarithm (base e) of a value.

number

Numeric expression. If null, the function returns null.

Description

Returns the logarithm of a value to a base. The input can be any numeric value, the return value is always a double. Logs of zero, negative numbers, and base of one return null as well as a warning.

Supported types

base	number	result
double	double	double
double	integer	double
double	long	double
double	unsigned_long	double
double		double
integer	double	double
integer	integer	double
integer	long	double
integer	unsigned_long	double
integer		double
long	double	double
long	integer	double
long	long	double
long	unsigned_long	double
long		double
unsigned_long	double	double
unsigned_long	integer	double
unsigned_long	long	double
unsigned_long	unsigned_long	double
unsigned_long		double

Examples

ROW base = 2.0, value = 8.0
| EVAL s = LOG(base, value)

base: double	value: double	s:double
2.0	8.0	3.0

row value = 100
| EVAL s = LOG(value);

value: integer	s:double
100	4.605170185988092

`LOG10`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the logarithm of a value to base 10. The input can be any numeric value, the return value is always a double. Logs of 0 and negative numbers return null as well as a warning.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW d = 1000.0
| EVAL s = LOG10(d)

d: double	s:double
1000.0	3.0

`PI`

Syntax

Parameters

Description

Returns Pi, the ratio of a circle’s circumference to its diameter.

Supported types

result
double

Example

ROW PI()

PI():double
3.141592653589793

`POW`

Syntax

Parameters

base

Numeric expression for the base. If null, the function returns null.

exponent

Numeric expression for the exponent. If null, the function returns null.

Description

Returns the value of base raised to the power of exponent.

It is still possible to overflow a double result here; in that case, null will be returned.

Supported types

base	exponent	result
double	double	double
double	integer	double
double	long	double
double	unsigned_long	double
integer	double	double
integer	integer	double
integer	long	double
integer	unsigned_long	double
long	double	double
long	integer	double
long	long	double
long	unsigned_long	double
unsigned_long	double	double
unsigned_long	integer	double
unsigned_long	long	double
unsigned_long	unsigned_long	double

Examples

ROW base = 2.0, exponent = 2
| EVAL result = POW(base, exponent)

base:double	exponent:integer	result:double
2.0	2	4.0

The exponent can be a fraction, which is similar to performing a root. For example, the exponent of 0.5 will give the square root of the base:

ROW base = 4, exponent = 0.5
| EVAL s = POW(base, exponent)

base:integer	exponent:double	s:double
4	0.5	2.0

`ROUND`

Syntax

Parameters

number

The numeric value to round. If null, the function returns null.

decimals

The number of decimal places to round to. Defaults to 0. If null, the function returns null.

Description

Rounds a number to the specified number of decimal places. Defaults to 0, which returns the nearest integer. If the precision is a negative number, rounds to the number of digits left of the decimal point.

Supported types

number	decimals	result
double	integer	double
double		double
integer	integer	integer
integer		integer
long	integer	long
long		long
unsigned_long		unsigned_long

Example

FROM employees
| KEEP first_name, last_name, height
| EVAL height_ft = ROUND(height * 3.281, 1)

first_name:keyword	last_name:keyword	height:double	height_ft:double
Arumugam	Ossenbruggen	2.1	6.9
Kwee	Schusler	2.1	6.9
Saniya	Kalloufi	2.1	6.9

`SIGNUM`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the sign of the given number. It returns -1 for negative numbers, 0 for 0 and 1 for positive numbers.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW d = 100.0
| EVAL s = SIGNUM(d)

d: double	s:double
100	1.0

`SIN`

Syntax

Parameters

angle

An angle, in radians. If null, the function returns null.

Description

Returns the sine of an angle.

Supported types

angle	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL sin=SIN(a)

a:double	sin:double
1.8	0.9738476308781951

`SINH`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the hyperbolic sine of a number.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL sinh=SINH(a)

a:double	sinh:double
1.8	2.94217428809568

`SQRT`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the square root of a number. The input can be any numeric value, the return value is always a double. Square roots of negative numbers and infinities are null.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW d = 100.0
| EVAL s = SQRT(d)

d: double	s:double
100.0	10.0

`TAN`

Syntax

Parameters

angle

An angle, in radians. If null, the function returns null.

Description

Returns the tangent of an angle.

Supported types

angle	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL tan=TAN(a)

a:double	tan:double
1.8	-4.286261674628062

`TANH`

Syntax

Parameters

number

Numeric expression. If null, the function returns null.

Description

Returns the hyperbolic tangent of a number.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=1.8
| EVAL tanh=TANH(a)

a:double	tanh:double
1.8	0.9468060128462683

`TAU`

Syntax

Parameters

Description

Returns the ratio of a circle’s circumference to its radius.

Supported types

result
double

Example

ROW TAU()

TAU():double
6.283185307179586

ES|QL Full-text search functions

ES|QL supports these full-text search functions:

[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. MATCH
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. QSTR

`MATCH`

Do not use on production environments. This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

Syntax

Parameters

field

Field that the query will target.

query

Text you wish to find in the provided field.

Description

Performs a match query on the specified field. Returns true if the provided query matches the row.

Supported types

field	query	result
keyword	keyword	boolean
keyword	text	boolean
text	keyword	boolean
text	text	boolean

Example

FROM books
| WHERE MATCH(author, "Faulkner")
| KEEP book_no, author
| SORT book_no
| LIMIT 5;

book_no:keyword	author:text
2378	[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott]
2713	William Faulkner
2847	Colleen Faulkner
2883	William Faulkner
3293	Danny Faulkner

`QSTR`

Syntax

Parameters

query

Query string in Lucene query string format.

Description

Performs a query string query. Returns true if the provided query string matches the row.

Supported types

query	result
keyword	boolean
text	boolean

Example

FROM books
| WHERE QSTR("author: Faulkner")
| KEEP book_no, author
| SORT book_no
| LIMIT 5;

book_no:keyword	author:text
2378	[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott]
2713	William Faulkner
2847	Colleen Faulkner
2883	William Faulkner
3293	Danny Faulkner

ES|QL spatial functions

ES|QL supports these spatial functions:

`ST_DISTANCE`

Syntax

Parameters

geomA

Expression of type geo_point or cartesian_point. If null, the function returns null.

geomB

Expression of type geo_point or cartesian_point. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_point and cartesian_point parameters.

Description

Computes the distance between two points. For cartesian geometries, this is the pythagorean distance in the same units as the original coordinates. For geographic geometries, this is the circular distance along the great circle in meters.

Supported types

geomA	geomB	result
cartesian_point	cartesian_point	double
geo_point	geo_point	double

Example

FROM airports
| WHERE abbrev == "CPH"
| EVAL distance = ST_DISTANCE(location, city_location)
| KEEP abbrev, name, location, city_location, distance

abbrev:k	name:text	location:geo_point	city_location:geo_point	distance:d
CPH	Copenhagen	POINT(12.6493508684508 55.6285017221528)	POINT(12.5683 55.6761)	7339.573896618216

`ST_INTERSECTS`

Syntax

Parameters

geomA

Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.

geomB

Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_* and cartesian_* parameters.

Description

Returns true if two geometries intersect. They intersect if they have any point in common, including their interior points (points along lines or within polygons). This is the inverse of the ST_DISJOINT function. In mathematical terms: ST_Intersects(A, B) ⇔ A ⋂ B ≠ ∅

Supported types

geomA	geomB	result
cartesian_point	cartesian_point	boolean
cartesian_point	cartesian_shape	boolean
cartesian_shape	cartesian_point	boolean
cartesian_shape	cartesian_shape	boolean
geo_point	geo_point	boolean
geo_point	geo_shape	boolean
geo_shape	geo_point	boolean
geo_shape	geo_shape	boolean

Example

FROM airports
| WHERE ST_INTERSECTS(location, TO_GEOSHAPE("POLYGON((42 14, 43 14, 43 15, 42 15, 42 14))"))

abbrev:keyword	city:keyword	city_location:geo_point	country:keyword	location:geo_point	name:text	scalerank:i	type:k
HOD	Al Ḩudaydah	POINT(42.9511 14.8022)	Yemen	POINT(42.97109630194 14.7552534413725)	Hodeidah Int’l	9	mid

`ST_DISJOINT`

Syntax

Parameters

geomA

Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.

geomB

Description

Returns whether the two geometries or geometry columns are disjoint. This is the inverse of the ST_INTERSECTS function. In mathematical terms: ST_Disjoint(A, B) ⇔ A ⋂ B = ∅

Supported types

geomA	geomB	result
cartesian_point	cartesian_point	boolean
cartesian_point	cartesian_shape	boolean
cartesian_shape	cartesian_point	boolean
cartesian_shape	cartesian_shape	boolean
geo_point	geo_point	boolean
geo_point	geo_shape	boolean
geo_shape	geo_point	boolean
geo_shape	geo_shape	boolean

Example

FROM airport_city_boundaries
| WHERE ST_DISJOINT(city_boundary, TO_GEOSHAPE("POLYGON((-10 -60, 120 -60, 120 60, -10 60, -10 -60))"))
| KEEP abbrev, airport, region, city, city_location

abbrev:keyword	airport:text	region:text	city:keyword	city_location:geo_point
ACA	General Juan N Alvarez Int’l	Acapulco de Juárez	Acapulco de Juárez	POINT (-99.8825 16.8636)

`ST_CONTAINS`

Syntax

Parameters

geomA

Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.

geomB

Description

Returns whether the first geometry contains the second geometry. This is the inverse of the ST_WITHIN function.

Supported types

geomA	geomB	result
cartesian_point	cartesian_point	boolean
cartesian_point	cartesian_shape	boolean
cartesian_shape	cartesian_point	boolean
cartesian_shape	cartesian_shape	boolean
geo_point	geo_point	boolean
geo_point	geo_shape	boolean
geo_shape	geo_point	boolean
geo_shape	geo_shape	boolean

Example

FROM airport_city_boundaries
| WHERE ST_CONTAINS(city_boundary, TO_GEOSHAPE("POLYGON((109.35 18.3, 109.45 18.3, 109.45 18.4, 109.35 18.4, 109.35 18.3))"))
| KEEP abbrev, airport, region, city, city_location

abbrev:keyword	airport:text	region:text	city:keyword	city_location:geo_point
SYX	Sanya Phoenix Int’l	天涯区	Sanya	POINT(109.5036 18.2533)

`ST_WITHIN`

Syntax

Parameters

geomA

Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.

geomB

Description

Returns whether the first geometry is within the second geometry. This is the inverse of the ST_CONTAINS function.

Supported types

geomA	geomB	result
cartesian_point	cartesian_point	boolean
cartesian_point	cartesian_shape	boolean
cartesian_shape	cartesian_point	boolean
cartesian_shape	cartesian_shape	boolean
geo_point	geo_point	boolean
geo_point	geo_shape	boolean
geo_shape	geo_point	boolean
geo_shape	geo_shape	boolean

Example

FROM airport_city_boundaries
| WHERE ST_WITHIN(city_boundary, TO_GEOSHAPE("POLYGON((109.1 18.15, 109.6 18.15, 109.6 18.65, 109.1 18.65, 109.1 18.15))"))
| KEEP abbrev, airport, region, city, city_location

abbrev:keyword	airport:text	region:text	city:keyword	city_location:geo_point
SYX	Sanya Phoenix Int’l	天涯区	Sanya	POINT(109.5036 18.2533)

`ST_X`

Syntax

Parameters

point

Expression of type geo_point or cartesian_point. If null, the function returns null.

Description

Extracts the x coordinate from the supplied point. If the points is of type geo_point this is equivalent to extracting the longitude value.

Supported types

point	result
cartesian_point	double
geo_point	double

Example

ROW point = TO_GEOPOINT("POINT(42.97109629958868 14.7552534006536)")
| EVAL x =  ST_X(point), y = ST_Y(point)

point:geo_point	x:double	y:double
POINT(42.97109629958868 14.7552534006536)	42.97109629958868	14.7552534006536

`ST_Y`

Syntax

Parameters

point

Expression of type geo_point or cartesian_point. If null, the function returns null.

Description

Extracts the y coordinate from the supplied point. If the points is of type geo_point this is equivalent to extracting the latitude value.

Supported types

point	result
cartesian_point	double
geo_point	double

Example

ROW point = TO_GEOPOINT("POINT(42.97109629958868 14.7552534006536)")
| EVAL x =  ST_X(point), y = ST_Y(point)

point:geo_point	x:double	y:double
POINT(42.97109629958868 14.7552534006536)	42.97109629958868	14.7552534006536

ES|QL string functions

ES|QL supports these string functions:

`BIT_LENGTH`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Returns the bit length of a string.

All strings are in UTF-8, so a single character can use multiple bytes.

Supported types

string	result
keyword	integer
text	integer

Example

FROM airports
| WHERE country == "India"
| KEEP city
| EVAL fn_length = LENGTH(city), fn_bit_length = BIT_LENGTH(city)

city:keyword	fn_length:integer	fn_bit_length:integer
Agwār	5	48
Ahmedabad	9	72
Bangalore	9	72

`BYTE_LENGTH`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Returns the byte length of a string.

All strings are in UTF-8, so a single character can use multiple bytes.

Supported types

string	result
keyword	integer
text	integer

Example

FROM airports
| WHERE country == "India"
| KEEP city
| EVAL fn_length = LENGTH(city), fn_byte_length = BYTE_LENGTH(city)

city:keyword	fn_length:integer	fn_byte_length:integer
Agwār	5	6
Ahmedabad	9	9
Bangalore	9	9

`CONCAT`

Syntax

Parameters

string1

Strings to concatenate.

string2

Strings to concatenate.

Description

Concatenates two or more strings.

Supported types

string1	string2	result
keyword	keyword	keyword
keyword	text	keyword
text	keyword	keyword
text	text	keyword

Example

FROM employees
| KEEP first_name, last_name
| EVAL fullname = CONCAT(first_name, " ", last_name)

first_name:keyword	last_name:keyword	fullname:keyword
Alejandro	McAlpine	Alejandro McAlpine
Amabile	Gomatam	Amabile Gomatam
Anneke	Preusig	Anneke Preusig

`ENDS_WITH`

Syntax

Parameters

str

String expression. If null, the function returns null.

suffix

String expression. If null, the function returns null.

Description

Returns a boolean that indicates whether a keyword string ends with another string.

Supported types

str	suffix	result
keyword	keyword	boolean
keyword	text	boolean
text	keyword	boolean
text	text	boolean

Example

FROM employees
| KEEP last_name
| EVAL ln_E = ENDS_WITH(last_name, "d")

last_name:keyword	ln_E:boolean
Awdeh	false
Azuma	false
Baek	false
Bamford	true
Bernatsky	false

`FROM_BASE64`

Syntax

Parameters

string

A base64 string.

Description

Decode a base64 string.

Supported types

string	result
keyword	keyword
text	keyword

Example

row a = "ZWxhc3RpYw=="
| eval d = from_base64(a)

a:keyword	d:keyword
ZWxhc3RpYw==	elastic

`LEFT`

Syntax

Parameters

string

The string from which to return a substring.

length

The number of characters to return.

Description

Returns the substring that extracts length chars from string starting from the left.

Supported types

string	length	result
keyword	integer	keyword
text	integer	keyword

Example

FROM employees
| KEEP last_name
| EVAL left = LEFT(last_name, 3)
| SORT last_name ASC
| LIMIT 5

last_name:keyword	left:keyword
Awdeh	Awd
Azuma	Azu
Baek	Bae
Bamford	Bam
Bernatsky	Ber

`LENGTH`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Returns the character length of a string.

All strings are in UTF-8, so a single character can use multiple bytes.

Supported types

string	result
keyword	integer
text	integer

Example

FROM airports
| WHERE country == "India"
| KEEP city
| EVAL fn_length = LENGTH(city)

city:keyword	fn_length:integer
Agwār	5
Ahmedabad	9
Bangalore	9

`LOCATE`

Syntax

Parameters

string

An input string

substring

A substring to locate in the input string

start

The start index

Description

Returns an integer that indicates the position of a keyword substring within another string. Returns 0 if the substring cannot be found. Note that string positions start from 1.

Supported types

string	substring	start	result
keyword	keyword	integer	integer
keyword	keyword		integer
keyword	text	integer	integer
keyword	text		integer
text	keyword	integer	integer
text	keyword		integer
text	text	integer	integer
text	text		integer

Example

row a = "hello"
| eval a_ll = locate(a, "ll")

a:keyword	a_ll:integer
hello	3

`LTRIM`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Removes leading whitespaces from a string.

Supported types

string	result
keyword	keyword
text	keyword

Example

ROW message = "   some text  ",  color = " red "
| EVAL message = LTRIM(message)
| EVAL color = LTRIM(color)
| EVAL message = CONCAT("'", message, "'")
| EVAL color = CONCAT("'", color, "'")

message:keyword	color:keyword
‘some text ‘	‘red ‘

`REPEAT`

Syntax

Parameters

string

String expression.

number

Number times to repeat.

Description

Returns a string constructed by concatenating string with itself the specified number of times.

Supported types

string	number	result
keyword	integer	keyword
text	integer	keyword

Example

ROW a = "Hello!"
| EVAL triple_a = REPEAT(a, 3);

a:keyword	triple_a:keyword
Hello!	Hello!Hello!Hello!

`REPLACE`

Syntax

Parameters

string

String expression.

regex

Regular expression.

newString

Replacement string.

Description

The function substitutes in the string str any match of the regular expression regex with the replacement string newStr.

Supported types

string	regex	newString	result
keyword	keyword	keyword	keyword
keyword	keyword	text	keyword
keyword	text	keyword	keyword
keyword	text	text	keyword
text	keyword	keyword	keyword
text	keyword	text	keyword
text	text	keyword	keyword
text	text	text	keyword

Example

This example replaces any occurrence of the word “World” with the word “Universe”:

ROW str = "Hello World"
| EVAL str = REPLACE(str, "World", "Universe")
| KEEP str

str:keyword
Hello Universe

`REVERSE`

Syntax

Parameters

str

String expression. If null, the function returns null.

Description

Returns a new string representing the input string in reverse order.

If Elasticsearch is running with a JDK version less than 20 then this will not properly reverse Grapheme Clusters. Elastic Cloud the JDK bundled with Elasticsearch all use newer JDKs. But if you’ve explicitly shifted to an older jdk then you’ll see things like “👍🏽😊” be reversed to “🏽👍😊” instead of the correct “😊👍🏽”.

Supported types

str	result
keyword	keyword
text	keyword

Examples

ROW message = "Some Text" | EVAL message_reversed = REVERSE(message);

message:keyword	message_reversed:keyword
Some Text	txeT emoS

REVERSE works with unicode, too! It keeps unicode grapheme clusters together during reversal.

ROW bending_arts = "💧🪨🔥💨" | EVAL bending_arts_reversed = REVERSE(bending_arts);

bending_arts:keyword	bending_arts_reversed:keyword
💧🪨🔥💨	💨🔥🪨💧

`RIGHT`

Syntax

Parameters

string

The string from which to returns a substring.

length

The number of characters to return.

Description

Return the substring that extracts length chars from str starting from the right.

Supported types

string	length	result
keyword	integer	keyword
text	integer	keyword

Example

FROM employees
| KEEP last_name
| EVAL right = RIGHT(last_name, 3)
| SORT last_name ASC
| LIMIT 5

last_name:keyword	right:keyword
Awdeh	deh
Azuma	uma
Baek	aek
Bamford	ord
Bernatsky	sky

`RTRIM`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Removes trailing whitespaces from a string.

Supported types

string	result
keyword	keyword
text	keyword

Example

ROW message = "   some text  ",  color = " red "
| EVAL message = RTRIM(message)
| EVAL color = RTRIM(color)
| EVAL message = CONCAT("'", message, "'")
| EVAL color = CONCAT("'", color, "'")

message:keyword	color:keyword
‘ some text’	‘ red’

`SPACE`

Syntax

Parameters

number

Number of spaces in result.

Description

Returns a string made of number spaces.

Supported types

number	result
integer	keyword

Example

ROW message = CONCAT("Hello", SPACE(1), "World!");

message:keyword
Hello World!

`SPLIT`

Syntax

Parameters

string

String expression. If null, the function returns null.

delim

Delimiter. Only single byte delimiters are currently supported.

Description

Split a single valued string into multiple strings.

Supported types

string	delim	result
keyword	keyword	keyword
keyword	text	keyword
text	keyword	keyword
text	text	keyword

Example

ROW words="foo;bar;baz;qux;quux;corge"
| EVAL word = SPLIT(words, ";")

words:keyword	word:keyword
foo;bar;baz;qux;quux;corge	[foo,bar,baz,qux,quux,corge]

`STARTS_WITH`

Syntax

Parameters

str

String expression. If null, the function returns null.

prefix

String expression. If null, the function returns null.

Description

Returns a boolean that indicates whether a keyword string starts with another string.

Supported types

str	prefix	result
keyword	keyword	boolean
keyword	text	boolean
text	keyword	boolean
text	text	boolean

Example

FROM employees
| KEEP last_name
| EVAL ln_S = STARTS_WITH(last_name, "B")

last_name:keyword	ln_S:boolean
Awdeh	false
Azuma	false
Baek	true
Bamford	true
Bernatsky	true

`SUBSTRING`

Syntax

Parameters

string

String expression. If null, the function returns null.

start

Start position.

length

Length of the substring from the start position. Optional; if omitted, all positions after start are returned.

Description

Returns a substring of a string, specified by a start position and an optional length.

Supported types

string	start	length	result
keyword	integer	integer	keyword
text	integer	integer	keyword

Examples

This example returns the first three characters of every last name:

FROM employees
| KEEP last_name
| EVAL ln_sub = SUBSTRING(last_name, 1, 3)

last_name:keyword	ln_sub:keyword
Awdeh	Awd
Azuma	Azu
Baek	Bae
Bamford	Bam
Bernatsky	Ber

A negative start position is interpreted as being relative to the end of the string. This example returns the last three characters of of every last name:

FROM employees
| KEEP last_name
| EVAL ln_sub = SUBSTRING(last_name, -3, 3)

last_name:keyword	ln_sub:keyword
Awdeh	deh
Azuma	uma
Baek	aek
Bamford	ord
Bernatsky	sky

If length is omitted, substring returns the remainder of the string. This example returns all characters except for the first:

FROM employees
| KEEP last_name
| EVAL ln_sub = SUBSTRING(last_name, 2)

last_name:keyword	ln_sub:keyword
Awdeh	wdeh
Azuma	zuma
Baek	aek
Bamford	amford
Bernatsky	ernatsky

`TO_BASE64`

Syntax

Parameters

string

A string.

Description

Encode a string to a base64 string.

Supported types

string	result
keyword	keyword
text	keyword

Example

row a = "elastic"
| eval e = to_base64(a)

a:keyword	e:keyword
elastic	ZWxhc3RpYw==

`TO_LOWER`

Syntax

Parameters

str

String expression. If null, the function returns null.

Description

Returns a new string representing the input string converted to lower case.

Supported types

str	result
keyword	keyword
text	keyword

Example

ROW message = "Some Text"
| EVAL message_lower = TO_LOWER(message)

message:keyword	message_lower:keyword
Some Text	some text

`TO_UPPER`

Syntax

Parameters

str

String expression. If null, the function returns null.

Description

Returns a new string representing the input string converted to upper case.

Supported types

str	result
keyword	keyword
text	keyword

Example

ROW message = "Some Text"
| EVAL message_upper = TO_UPPER(message)

message:keyword	message_upper:keyword
Some Text	SOME TEXT

`TRIM`

Syntax

Parameters

string

String expression. If null, the function returns null.

Description

Removes leading and trailing whitespaces from a string.

Supported types

string	result
keyword	keyword
text	keyword

Example

ROW message = "   some text  ",  color = " red "
| EVAL message = TRIM(message)
| EVAL color = TRIM(color)

message:s	color:s
some text	red

ES|QL type conversion functions

ES|QL supports implicit casting from string literals to certain data types. Refer to implicit casting for details.

ES|QL supports these type conversion functions:

TO_BOOLEAN
TO_CARTESIANPOINT
TO_CARTESIANSHAPE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_DATEPERIOD
TO_DATETIME
TO_DEGREES
TO_DOUBLE
TO_GEOPOINT
TO_GEOSHAPE
TO_INTEGER
TO_IP
TO_LONG
TO_RADIANS
TO_STRING
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_TIMEDURATION
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. TO_UNSIGNED_LONG
TO_VERSION

`TO_BOOLEAN`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a boolean value. A string value of true will be case-insensitive converted to the Boolean true. For anything else, including the empty string, the function will return false. The numerical value of 0 will be converted to false, anything else will be converted to true.

Supported types

field	result
boolean	boolean
double	boolean
integer	boolean
keyword	boolean
long	boolean
text	boolean
unsigned_long	boolean

Example

ROW str = ["true", "TRuE", "false", "", "yes", "1"]
| EVAL bool = TO_BOOLEAN(str)

str:keyword	bool:boolean
[“true”, “TRuE”, “false”, “”, “yes”, “1”]	[true, true, false, false, false, false]

`TO_CARTESIANPOINT`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a cartesian_point value. A string will only be successfully converted if it respects the WKT Point format.

Supported types

field	result
cartesian_point	cartesian_point
keyword	cartesian_point
text	cartesian_point

Example

ROW wkt = ["POINT(4297.11 -1475.53)", "POINT(7580.93 2272.77)"]
| MV_EXPAND wkt
| EVAL pt = TO_CARTESIANPOINT(wkt)

wkt:keyword	pt:cartesian_point
“POINT(4297.11 -1475.53)”	POINT(4297.11 -1475.53)
“POINT(7580.93 2272.77)”	POINT(7580.93 2272.77)

`TO_CARTESIANSHAPE`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a cartesian_shape value. A string will only be successfully converted if it respects the WKT format.

Supported types

field	result
cartesian_point	cartesian_shape
cartesian_shape	cartesian_shape
keyword	cartesian_shape
text	cartesian_shape

Example

ROW wkt = ["POINT(4297.11 -1475.53)", "POLYGON ((3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97))"]
| MV_EXPAND wkt
| EVAL geom = TO_CARTESIANSHAPE(wkt)

wkt:keyword	geom:cartesian_shape
“POINT(4297.11 -1475.53)”	POINT(4297.11 -1475.53)
“POLYGON 3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97”	POLYGON 3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97

`TO_DATEPERIOD`

Syntax

Parameters

field

Input value. The input is a valid constant date period expression.

Description

Converts an input value into a date_period value.

Supported types

field	result
date_period	date_period
keyword	date_period
text	date_period

Example

row x = "2024-01-01"::datetime | eval y = x + "3 DAYS"::date_period, z = x - to_dateperiod("3 days");

x:datetime	y:datetime	z:datetime
2024-01-01	2024-01-04	2023-12-29

`TO_DATETIME`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a date value. A string will only be successfully converted if it’s respecting the format yyyy-MM-dd'T'HH:mm:ss.SSS'Z'. To convert dates in other formats, use DATE_PARSE.

Note that when converting from nanosecond resolution to millisecond resolution with this function, the nanosecond date is truncated, not rounded.

Supported types

field	result
date	date
date_nanos	date
double	date
integer	date
keyword	date
long	date
text	date
unsigned_long	date

Examples

ROW string = ["1953-09-02T00:00:00.000Z", "1964-06-02T00:00:00.000Z", "1964-06-02 00:00:00"]
| EVAL datetime = TO_DATETIME(string)

string:keyword	datetime:date
[“1953-09-02T00:00:00.000Z”, “1964-06-02T00:00:00.000Z”, “1964-06-02 00:00:00”]	[1953-09-02T00:00:00.000Z, 1964-06-02T00:00:00.000Z]

Note that in this example, the last value in the source multi-valued field has not been converted. The reason being that if the date format is not respected, the conversion will result in a null value. When this happens a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:112: evaluation of [TO_DATETIME(string)] failed, treating result as null. "Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.IllegalArgumentException: failed to parse date field [1964-06-02 00:00:00] with format [yyyy-MM-dd'T'HH:mm:ss.SSS'Z']"

If the input parameter is of a numeric type, its value will be interpreted as milliseconds since the Unix epoch. For example:

ROW int = [0, 1]
| EVAL dt = TO_DATETIME(int)

int:integer	dt:date
[0, 1]	[1970-01-01T00:00:00.000Z, 1970-01-01T00:00:00.001Z]

`TO_DEGREES`

Syntax

Parameters

number

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts a number in radians to degrees).

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW rad = [1.57, 3.14, 4.71]
| EVAL deg = TO_DEGREES(rad)

rad:double	deg:double
[1.57, 3.14, 4.71]	[89.95437383553924, 179.9087476710785, 269.86312150661774]

`TO_DOUBLE`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a double value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to double. Boolean true will be converted to double 1.0, false to 0.0.

Supported types

field	result
boolean	double
counter_double	double
counter_integer	double
counter_long	double
date	double
double	double
integer	double
keyword	double
long	double
text	double
unsigned_long	double

Example

ROW str1 = "5.20128E11", str2 = "foo"
| EVAL dbl = TO_DOUBLE("520128000000"), dbl1 = TO_DOUBLE(str1), dbl2 = TO_DOUBLE(str2)

str1:keyword	str2:keyword	dbl:double	dbl1:double	dbl2:double
5.20128E11	foo	5.20128E11	5.20128E11	null

Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:115: evaluation of [TO_DOUBLE(str2)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value: "java.lang.NumberFormatException: For input string: "foo""

`TO_GEOPOINT`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a geo_point value. A string will only be successfully converted if it respects the WKT Point format.

Supported types

field	result
geo_point	geo_point
keyword	geo_point
text	geo_point

Example

ROW wkt = "POINT(42.97109630194 14.7552534413725)"
| EVAL pt = TO_GEOPOINT(wkt)

wkt:keyword	pt:geo_point
“POINT(42.97109630194 14.7552534413725)”	POINT(42.97109630194 14.7552534413725)

`TO_GEOSHAPE`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a geo_shape value. A string will only be successfully converted if it respects the WKT format.

Supported types

field	result
geo_point	geo_shape
geo_shape	geo_shape
keyword	geo_shape
text	geo_shape

Example

ROW wkt = "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))"
| EVAL geom = TO_GEOSHAPE(wkt)

wkt:keyword	geom:geo_shape
“POLYGON 30 10, 40 40, 20 40, 10 20, 30 10”	POLYGON 30 10, 40 40, 20 40, 10 20, 30 10

`TO_INTEGER`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to an integer value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to integer. Boolean true will be converted to integer 1, false to 0.

Supported types

field	result
boolean	integer
counter_integer	integer
date	integer
double	integer
integer	integer
keyword	integer
long	integer
text	integer
unsigned_long	integer

Example

ROW long = [5013792, 2147483647, 501379200000]
| EVAL int = TO_INTEGER(long)

long:long	int:integer
[5013792, 2147483647, 501379200000]	[5013792, 2147483647]

Note that in this example, the last value of the multi-valued field cannot be converted as an integer. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:61: evaluation of [TO_INTEGER(long)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"org.elasticsearch.xpack.esql.core.InvalidArgumentException: [501379200000] out of [integer] range"

`TO_IP`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input string to an IP value.

Supported types

field	result
ip	ip
keyword	ip
text	ip

Example

ROW str1 = "1.1.1.1", str2 = "foo"
| EVAL ip1 = TO_IP(str1), ip2 = TO_IP(str2)
| WHERE CIDR_MATCH(ip1, "1.0.0.0/8")

str1:keyword	str2:keyword	ip1:ip	ip2:ip
1.1.1.1	foo	1.1.1.1	null

"Line 1:68: evaluation of [TO_IP(str2)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.IllegalArgumentException: 'foo' is not an IP string literal."

`TO_LONG`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to long. Boolean true will be converted to long 1, false to 0.

Supported types

field	result
boolean	long
counter_integer	long
counter_long	long
date	long
date_nanos	long
double	long
integer	long
keyword	long
long	long
text	long
unsigned_long	long

Example

ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo"
| EVAL long1 = TO_LONG(str1), long2 = TO_LONG(str2), long3 = TO_LONG(str3)

str1:keyword	str2:keyword	str3:keyword	long1:long	long2:long	long3:long
2147483648	2147483648.2	foo	2147483648	2147483648	null

"Line 1:113: evaluation of [TO_LONG(str3)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.NumberFormatException: For input string: "foo""

`TO_RADIANS`

Syntax

Parameters

number

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts a number in degrees) to radians.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW deg = [90.0, 180.0, 270.0]
| EVAL rad = TO_RADIANS(deg)

deg:double	rad:double
[90.0, 180.0, 270.0]	[1.5707963267948966, 3.141592653589793, 4.71238898038469]

`TO_STRING`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value into a string.

Supported types

field	result
boolean	keyword
cartesian_point	keyword
cartesian_shape	keyword
date	keyword
date_nanos	keyword
double	keyword
geo_point	keyword
geo_shape	keyword
integer	keyword
ip	keyword
keyword	keyword
long	keyword
text	keyword
unsigned_long	keyword
version	keyword

Examples

ROW a=10
| EVAL j = TO_STRING(a)

a:integer	j:keyword
10	“10”

It also works fine on multivalued fields:

ROW a=[10, 9, 8]
| EVAL j = TO_STRING(a)

a:integer	j:keyword
[10, 9, 8]	[“10”, “9”, “8”]

`TO_TIMEDURATION`

Syntax

Parameters

field

Input value. The input is a valid constant time duration expression.

Description

Converts an input value into a time_duration value.

Supported types

field	result
keyword	time_duration
text	time_duration
time_duration	time_duration

Example

row x = "2024-01-01"::datetime | eval y = x + "3 hours"::time_duration, z = x - to_timeduration("3 hours");

x:datetime	y:datetime	z:datetime
2024-01-01	2024-01-01T03:00:00.000Z	2023-12-31T21:00:00.000Z

`TO_UNSIGNED_LONG`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to an unsigned long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to unsigned long. Boolean true will be converted to unsigned long 1, false to 0.

Supported types

field	result
boolean	unsigned_long
date	unsigned_long
double	unsigned_long
integer	unsigned_long
keyword	unsigned_long
long	unsigned_long
text	unsigned_long
unsigned_long	unsigned_long

Example

ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo"
| EVAL long1 = TO_UNSIGNED_LONG(str1), long2 = TO_ULONG(str2), long3 = TO_UL(str3)

str1:keyword	str2:keyword	str3:keyword	long1:unsigned_long	long2:unsigned_long	long3:unsigned_long
2147483648	2147483648.2	foo	2147483648	2147483648	null

"Line 1:133: evaluation of [TO_UL(str3)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.NumberFormatException: Character f is neither a decimal digit number, decimal point, + "nor "e" notation exponential mark."

`TO_VERSION`

Syntax

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input string to a version value.

Supported types

field	result
keyword	version
text	version
version	version

Example

ROW v = TO_VERSION("1.2.3")

v:version
1.2.3

ES|QL multivalue functions

ES|QL supports these multivalue functions:

`MV_APPEND`

Syntax

Parameters

field1

field2

Description

Concatenates values of two multi-value fields.

Supported types

field1	field2	result
boolean	boolean	boolean
cartesian_point	cartesian_point	cartesian_point
cartesian_shape	cartesian_shape	cartesian_shape
date	date	date
double	double	double
geo_point	geo_point	geo_point
geo_shape	geo_shape	geo_shape
integer	integer	integer
ip	ip	ip
keyword	keyword	keyword
long	long	long
text	text	keyword
version	version	version

`MV_AVG`

Syntax

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the average of all of the values.

Supported types

number	result
double	double
integer	double
long	double
unsigned_long	double

Example

ROW a=[3, 5, 1, 6]
| EVAL avg_a = MV_AVG(a)

a:integer	avg_a:double
[3, 5, 1, 6]	3.75

`MV_CONCAT`

Syntax

Parameters

string

Multivalue expression.

delim

Delimiter.

Description

Converts a multivalued string expression into a single valued column containing the concatenation of all values separated by a delimiter.

Supported types

string	delim	result
keyword	keyword	keyword
keyword	text	keyword
text	keyword	keyword
text	text	keyword

Examples

ROW a=["foo", "zoo", "bar"]
| EVAL j = MV_CONCAT(a, ", ")

a:keyword	j:keyword
[“foo”, “zoo”, “bar”]	“foo, zoo, bar”

To concat non-string columns, call TO_STRING first:

ROW a=[10, 9, 8]
| EVAL j = MV_CONCAT(TO_STRING(a), ", ")

a:integer	j:keyword
[10, 9, 8]	“10, 9, 8”

`MV_COUNT`

Syntax

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing a count of the number of values.

Supported types

field	result
boolean	integer
cartesian_point	integer
cartesian_shape	integer
date	integer
date_nanos	integer
double	integer
geo_point	integer
geo_shape	integer
integer	integer
ip	integer
keyword	integer
long	integer
text	integer
unsigned_long	integer
version	integer

Example

ROW a=["foo", "zoo", "bar"]
| EVAL count_a = MV_COUNT(a)

a:keyword	count_a:integer
[“foo”, “zoo”, “bar”]	3

`MV_DEDUPE`

Syntax

Parameters

field

Multivalue expression.

Description

Remove duplicate values from a multivalued field.

MV_DEDUPE may, but won’t always, sort the values in the column.

Supported types

field	result
boolean	boolean
cartesian_point	cartesian_point
cartesian_shape	cartesian_shape
date	date
date_nanos	date_nanos
double	double
geo_point	geo_point
geo_shape	geo_shape
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
version	version

Example

ROW a=["foo", "foo", "bar", "foo"]
| EVAL dedupe_a = MV_DEDUPE(a)

a:keyword	dedupe_a:keyword
[“foo”, “foo”, “bar”, “foo”]	[“foo”, “bar”]

`MV_FIRST`

Syntax

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the first value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that. If you need the minimum value use MV_MIN instead of MV_FIRST. MV_MIN has optimizations for sorted values so there isn’t a performance benefit to MV_FIRST.

Supported types

field	result
boolean	boolean
cartesian_point	cartesian_point
cartesian_shape	cartesian_shape
date	date
date_nanos	date_nanos
double	double
geo_point	geo_point
geo_shape	geo_shape
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long	unsigned_long
version	version

Example

ROW a="foo;bar;baz"
| EVAL first_a = MV_FIRST(SPLIT(a, ";"))

a:keyword	first_a:keyword
foo;bar;baz	“foo”

`MV_LAST`

Syntax

Parameters

field

Multivalue expression.

Description

Converts a multivalue expression into a single valued column containing the last value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that. If you need the maximum value use MV_MAX instead of MV_LAST. MV_MAX has optimizations for sorted values so there isn’t a performance benefit to MV_LAST.

Supported types

field	result
boolean	boolean
cartesian_point	cartesian_point
cartesian_shape	cartesian_shape
date	date
date_nanos	date_nanos
double	double
geo_point	geo_point
geo_shape	geo_shape
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long	unsigned_long
version	version

Example

ROW a="foo;bar;baz"
| EVAL last_a = MV_LAST(SPLIT(a, ";"))

a:keyword	last_a:keyword
foo;bar;baz	“baz”

`MV_MAX`

Syntax

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the maximum value.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long	unsigned_long
version	version

Examples

ROW a=[3, 5, 1]
| EVAL max_a = MV_MAX(a)

a:integer	max_a:integer
[3, 5, 1]	5

It can be used by any column type, including keyword columns. In that case it picks the last string, comparing their utf-8 representation byte by byte:

ROW a=["foo", "zoo", "bar"]
| EVAL max_a = MV_MAX(a)

a:keyword	max_a:keyword
[“foo”, “zoo”, “bar”]	“zoo”

`MV_MEDIAN`

Syntax

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the median value.

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Examples

ROW a=[3, 5, 1]
| EVAL median_a = MV_MEDIAN(a)

a:integer	median_a:integer
[3, 5, 1]	3

If the row has an even number of values for a column, the result will be the average of the middle two entries. If the column is not floating point, the average rounds down:

ROW a=[3, 7, 1, 6]
| EVAL median_a = MV_MEDIAN(a)

a:integer	median_a:integer
[3, 7, 1, 6]	4

`MV_MEDIAN_ABSOLUTE_DEVIATION`

Syntax

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the median absolute deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X, the median absolute deviation is median(|median(X) - X|).

If the field has an even number of values, the medians will be calculated as the average of the middle two values. If the value is not a floating point number, the averages are rounded towards 0.

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Example

ROW values = [0, 2, 5, 6]
| EVAL median_absolute_deviation = MV_MEDIAN_ABSOLUTE_DEVIATION(values), median = MV_MEDIAN(values)

values:integer	median_absolute_deviation:integer	median:integer
[0, 2, 5, 6]	2	3

`MV_MIN`

Syntax

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the minimum value.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long	unsigned_long
version	version

Examples

ROW a=[2, 1]
| EVAL min_a = MV_MIN(a)

a:integer	min_a:integer
[2, 1]	1

It can be used by any column type, including keyword columns. In that case, it picks the first string, comparing their utf-8 representation byte by byte:

ROW a=["foo", "bar"]
| EVAL min_a = MV_MIN(a)

a:keyword	min_a:keyword
[“foo”, “bar”]	“bar”

`MV_PERCENTILE`

Syntax

Parameters

number

Multivalue expression.

percentile

The percentile to calculate. Must be a number between 0 and 100. Numbers out of range will return a null instead.

Description

Converts a multivalued field into a single valued field containing the value at which a certain percentage of observed values occur.

Supported types

number	percentile	result
double	double	double
double	integer	double
double	long	double
integer	double	integer
integer	integer	integer
integer	long	integer
long	double	long
long	integer	long
long	long	long

Example

ROW values = [5, 5, 10, 12, 5000]
| EVAL p50 = MV_PERCENTILE(values, 50), median = MV_MEDIAN(values)

values:integer	p50:integer	median:integer
[5, 5, 10, 12, 5000]	10	10

`MV_PSERIES_WEIGHTED_SUM`

Syntax

Parameters

number

Multivalue expression.

p

It is a constant number that represents the p parameter in the P-Series. It impacts every element’s contribution to the weighted sum.

Description

Converts a multivalued expression into a single-valued column by multiplying every element on the input list by its corresponding term in P-Series and computing the sum.

Supported types

number	p	result
double	double	double

Example

ROW a = [70.0, 45.0, 21.0, 21.0, 21.0]
| EVAL sum = MV_PSERIES_WEIGHTED_SUM(a, 1.5)
| KEEP sum

sum:double
94.45465156212452

`MV_SLICE`

Syntax

Parameters

field

Multivalue expression. If null, the function returns null.

start

Start position. If null, the function returns null. The start argument can be negative. An index of -1 is used to specify the last value in the list.

end

End position(included). Optional; if omitted, the position at start is returned. The end argument can be negative. An index of -1 is used to specify the last value in the list.

Description

Returns a subset of the multivalued field using the start and end index values. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT or MV_SORT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that.

Supported types

field	start	end	result
boolean	integer	integer	boolean
cartesian_point	integer	integer	cartesian_point
cartesian_shape	integer	integer	cartesian_shape
date	integer	integer	date
date_nanos	integer	integer	date_nanos
double	integer	integer	double
geo_point	integer	integer	geo_point
geo_shape	integer	integer	geo_shape
integer	integer	integer	integer
ip	integer	integer	ip
keyword	integer	integer	keyword
long	integer	integer	long
text	integer	integer	keyword
version	integer	integer	version

Examples

row a = [1, 2, 2, 3]
| eval a1 = mv_slice(a, 1), a2 = mv_slice(a, 2, 3)

a:integer	a1:integer	a2:integer
[1, 2, 2, 3]	2	[2, 3]

row a = [1, 2, 2, 3]
| eval a1 = mv_slice(a, -2), a2 = mv_slice(a, -3, -1)

a:integer	a1:integer	a2:integer
[1, 2, 2, 3]	2	[2, 2, 3]

`MV_SORT`

Syntax

Parameters

field

Multivalue expression. If null, the function returns null.

order

Sort order. The valid options are ASC and DESC, the default is ASC.

Description

Sorts a multivalued field in lexicographical order.

Supported types

field	order	result
boolean	keyword	boolean
date	keyword	date
date_nanos	keyword	date_nanos
double	keyword	double
integer	keyword	integer
ip	keyword	ip
keyword	keyword	keyword
long	keyword	long
text	keyword	keyword
version	keyword	version

Example

ROW a = [4, 2, -3, 2]
| EVAL sa = mv_sort(a), sd = mv_sort(a, "DESC")

a:integer	sa:integer	sd:integer
[4, 2, -3, 2]	[-3, 2, 2, 4]	[4, 2, 2, -3]

`MV_SUM`

Syntax

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the sum of all of the values.

Supported types

number	result
double	double
integer	integer
long	long
unsigned_long	unsigned_long

Example

ROW a=[3, 5, 6]
| EVAL sum_a = MV_SUM(a)

a:integer	sum_a:integer
[3, 5, 6]	14

`MV_ZIP`

Syntax

Parameters

string1

Multivalue expression.

string2

Multivalue expression.

delim

Delimiter. Optional; if omitted, , is used as a default delimiter.

Description

Combines the values from two multivalued fields with a delimiter that joins them together.

Supported types

string1	string2	delim	result
keyword	keyword	keyword	keyword
keyword	keyword	text	keyword
keyword	keyword		keyword
keyword	text	keyword	keyword
keyword	text	text	keyword
keyword	text		keyword
text	keyword	keyword	keyword
text	keyword	text	keyword
text	keyword		keyword
text	text	keyword	keyword
text	text	text	keyword
text	text		keyword

Example

ROW a = ["x", "y", "z"], b = ["1", "2"]
| EVAL c = mv_zip(a, b, "-")
| KEEP a, b, c

a:keyword	b:keyword	c:keyword
[x, y, z]	[1 ,2]	[x-1, y-2, z]

ES|QL operators

Boolean operators for comparing against one or multiple expressions.

Binary operators
Unary operators
Logical operators
IS NULL and IS NOT NULL predicates
Cast (::)
IN
LIKE
RLIKE
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Search operators

lhs	rhs	result
date	date	boolean
date_nanos	date_nanos	boolean
double	double	boolean
double	integer	boolean
double	long	boolean
integer	double	boolean
integer	integer	boolean
integer	long	boolean
ip	ip	boolean
keyword	keyword	boolean
keyword	text	boolean
long	double	boolean
long	integer	boolean
long	long	boolean
text	keyword	boolean
text	text	boolean
unsigned_long	unsigned_long	boolean
version	version	boolean

Add `+`

Add two numbers together. If either field is multivalued then the result is null.

Supported types:

Supported types

lhs	rhs	result
date	date_period	date
date	time_duration	date
date_period	date	date
date_period	date_period	date_period
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	integer
integer	long	long
long	double	double
long	integer	long
long	long	long
time_duration	date	date
time_duration	time_duration	time_duration
unsigned_long	unsigned_long	unsigned_long

Subtract `-`

Subtract one number from another. If either field is multivalued then the result is null.

Supported types:

Supported types

lhs	rhs	result
date	date_period	date
date	time_duration	date
date_period	date_period	date_period
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	integer
integer	long	long
long	double	double
long	integer	long
long	long	long
time_duration	time_duration	time_duration
unsigned_long	unsigned_long	unsigned_long

Multiply `*`

Multiply two numbers together. If either field is multivalued then the result is null.

Supported types:

Supported types

lhs	rhs	result
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	integer
integer	long	long
long	double	double
long	integer	long
long	long	long
unsigned_long	unsigned_long	unsigned_long

Divide `/`

Divide one number by another. If either field is multivalued then the result is null.

Division of two integer types will yield an integer result, rounding towards 0. If you need floating point division, Cast (::) one of the arguments to a DOUBLE.

Supported types:

Supported types

lhs	rhs	result
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	integer
integer	long	long
long	double	double
long	integer	long
long	long	long
unsigned_long	unsigned_long	unsigned_long

Modulus `%`

Divide one number by another and return the remainder. If either field is multivalued then the result is null.

Supported types:

Supported types

lhs	rhs	result
double	double	double
double	integer	double
double	long	double
integer	double	double
integer	integer	integer
integer	long	long
long	double	double
long	integer	long
long	long	long
unsigned_long	unsigned_long	unsigned_long

Unary operators

The only unary operators is negation (-):

Supported types:

Supported types

field	result
date_period	date_period
double	double
integer	integer
long	long
time_duration	time_duration

Logical operators

The following logical operators are supported:

AND
OR
NOT

`IS NULL` and `IS NOT NULL` predicates

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

FROM employees
| WHERE birth_date IS NULL
| KEEP first_name, last_name
| SORT first_name
| LIMIT 3

first_name:keyword	last_name:keyword
Basil	Tramer
Florian	Syrotiuk
Lucien	Rosenbaum

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

COUNT(emp_no):long
84

`Cast (::)`

The :: operator provides a convenient alternative syntax to the TO_<type> conversion functions.

ROW ver = CONCAT(("0"::INT + 1)::STRING, ".2.3")::VERSION

ver:version
1.2.3

`IN`

The IN operator allows testing whether a field or expression equals an element in a list of literals, fields or expressions:

ROW a = 1, b = 4, c = 3
| WHERE c-a IN (3, b / 2, a)

a:integer	b:integer	c:integer
1	4	3

`LIKE`

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

str	pattern	result
keyword	keyword	boolean
text	text	boolean

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

first_name:keyword	last_name:keyword
Ebbe	Callaway
Eberhardt	Terkki

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

`RLIKE`

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

str	pattern	result
keyword	keyword	boolean
text	text	boolean

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

first_name:keyword	last_name:keyword
Alejandro	McAlpine

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

Search operators

The only search operator is match (:).

The match operator performs a match query on the specified field. Returns true if the provided query matches the row.

Supported types

field	query	result
keyword	keyword	boolean
keyword	text	boolean
text	keyword	boolean
text	text	boolean

FROM books
| WHERE author:"Faulkner"
| KEEP book_no, author
| SORT book_no
| LIMIT 5;

book_no:keyword	author:text
2378	[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott]
2713	William Faulkner
2847	Colleen Faulkner
2883	William Faulkner
3293	Danny Faulkner

Functions and operators

ES|QL functions and operators