ES|QL functions and operators

edit

ES|QL functions and operators

edit

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

edit
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
  • [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. WEIGHTED_AVG
Grouping functions
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
  • [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_INTERSECTS
  • [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_DISJOINT
  • [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_CONTAINS
  • [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_WITHIN
  • [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_X
  • [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_Y
  • [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_DISTANCE
String functions
Type conversion functions
Multi value functions

ES|QL aggregate functions

edit

The STATS ... BY 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
  • [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. WEIGHTED_AVG

AVG

edit

Syntax

AVG(number)

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

edit

Syntax

COUNT(field)

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)

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

edit

Syntax

COUNT_DISTINCT(field,precision)

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

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

edit

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:

cardinality error

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

edit

Syntax

MAX(field)

Parameters

field

Description

The maximum value of a field.

Supported types

field result

boolean

boolean

date

date

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

edit

Syntax

MEDIAN(number)

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

edit

Syntax

MEDIAN_ABSOLUTE_DEVIATION(number)

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

edit

Syntax

MIN(field)

Parameters

field

Description

The minimum value of a field.

Supported types

field result

boolean

boolean

date

date

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

edit

Syntax

PERCENTILE(number,percentile)

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

edit

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:

percentiles error

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

edit

Syntax

ST_CENTROID_AGG(field)

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

edit

Syntax

SUM(number)

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

edit

Syntax

TOP(field,limit,order)

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

edit

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

VALUES(field)

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

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

edit

Syntax

WEIGHTED_AVG(number,weight)

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

edit

The STATS ... BY command supports these grouping functions:

BUCKET

edit

Syntax

BUCKET(field,buckets,from,to)

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

edit

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

CASE

edit

Syntax

CASE(condition,trueValueelseValue)

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

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

edit

Syntax

COALESCE(first,rest)

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

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

edit

Syntax

GREATEST(first,rest)

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

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

edit

Syntax

LEAST(first,rest)

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

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

edit

ES|QL supports these date-time functions:

DATE_DIFF

edit

Syntax

DATE_DIFF(unit,startTimestamp,endTimestamp)

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

edit

Syntax

DATE_EXTRACT(datePart,date)

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

edit

Syntax

DATE_FORMAT(dateFormat,date)

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

edit

Syntax

DATE_PARSE(datePattern,dateString)

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

edit

Syntax

DATE_TRUNC(interval,date)

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

time_duration

date

date

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 ... BY 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

edit

Syntax

NOW()

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

edit

ES|QL supports these IP functions:

CIDR_MATCH

edit

Syntax

CIDR_MATCH(ip,blockX)

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

edit

Syntax

IP_PREFIX(ip,prefixLengthV4,prefixLengthV6)

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

edit

ES|QL supports these mathematical functions:

ABS

edit

Syntax

ABS(number)

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

edit

Syntax

ACOS(number)

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

edit

Syntax

ASIN(number)

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

edit

Syntax

ATAN(number)

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

edit

Syntax

ATAN2(y_coordinate,x_coordinate)

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

edit

Syntax

CBRT(number)

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

edit

Syntax

CEIL(number)

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

edit

Syntax

COS(angle)

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

edit

Syntax

COSH(number)

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

Syntax

E()

Parameters

Description

Returns Euler’s number.

Supported types

result

double

Example

ROW E()
E():double

2.718281828459045

EXP

edit

Syntax

EXP(number)

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

edit

Syntax

FLOOR(number)

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

edit

Syntax

HYPOT(number1,number2)

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

edit

Syntax

LOG(base,number)

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

edit

Syntax

LOG10(number)

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

Syntax

PI()

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

edit

Syntax

POW(base,exponent)

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

edit

Syntax

ROUND(number,decimals)

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

edit

Syntax

SIGNUM(number)

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

edit

Syntax

SIN(angle)

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

edit

Syntax

SINH(number)

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

edit

Syntax

SQRT(number)

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

edit

Syntax

TAN(angle)

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

edit

Syntax

TANH(number)

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

edit

Syntax

TAU()

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

edit

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

edit

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

MATCH(field,query)

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

edit

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

QSTR(query)

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

edit

ES|QL supports these spatial 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. ST_INTERSECTS
  • [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_DISJOINT
  • [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_CONTAINS
  • [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_WITHIN
  • [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_X
  • [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_Y
  • [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_DISTANCE

ST_INTERSECTS

edit

Syntax

ST_INTERSECTS(geomA,geomB)

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

edit

Syntax

ST_DISJOINT(geomA,geomB)

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

edit

Syntax

ST_CONTAINS(geomA,geomB)

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

edit

Syntax

ST_WITHIN(geomA,geomB)

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

edit

Syntax

ST_X(point)

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

edit

Syntax

ST_Y(point)

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

ST_DISTANCE

edit

Syntax

ST_DISTANCE(geomA,geomB)

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

ES|QL string functions

edit

ES|QL supports these string functions:

BIT_LENGTH

edit

Syntax

BIT_LENGTH(string)

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

edit

Syntax

BYTE_LENGTH(string)

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

edit

Syntax

CONCAT(string1,string2)

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

edit

Syntax

ENDS_WITH(str,suffix)

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

edit

Syntax

FROM_BASE64(string)

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

edit

Syntax

LEFT(string,length)

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

edit

Syntax

LENGTH(string)

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

edit

Syntax

LOCATE(string,substring,start)

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

edit

Syntax

LTRIM(string)

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

edit

Syntax

REPEAT(string,number)

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

edit

Syntax

REPLACE(string,regex,newString)

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

edit

Syntax

REVERSE(str)

Parameters

str
String expression. If null, the function returns null.

Description

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

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

edit

Syntax

RIGHT(string,length)

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

edit

Syntax

RTRIM(string)

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

edit

Syntax

SPACE(number)

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

edit

Syntax

SPLIT(string,delim)

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

edit

Syntax

STARTS_WITH(str,prefix)

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

edit

Syntax

SUBSTRING(string,start,length)

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

edit

Syntax

TO_BASE64(string)

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

edit

Syntax

TO_LOWER(str)

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

edit

Syntax

TO_UPPER(str)

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

edit

Syntax

TRIM(string)

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

edit

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

edit

Syntax

TO_BOOLEAN(field)

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

edit

Syntax

TO_CARTESIANPOINT(field)

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

edit

Syntax

TO_CARTESIANSHAPE(field)

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

edit

Syntax

TO_DATEPERIOD(field)

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

edit

Syntax

TO_DATETIME(field)

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

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

edit

Syntax

TO_DEGREES(number)

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

edit

Syntax

TO_DOUBLE(field)

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

edit

Syntax

TO_GEOPOINT(field)

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

edit

Syntax

TO_GEOSHAPE(field)

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

edit

Syntax

TO_INTEGER(field)

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

edit

Syntax

TO_IP(field)

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

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

edit

Syntax

TO_LONG(field)

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

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

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

edit

Syntax

TO_RADIANS(number)

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

edit

Syntax

TO_STRING(field)

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

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

edit

Syntax

TO_TIMEDURATION(field)

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

edit

Syntax

TO_UNSIGNED_LONG(field)

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

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

edit

Syntax

TO_VERSION(field)

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

edit

ES|QL supports these multivalue functions:

MV_APPEND

edit

Syntax

MV_APPEND(field1,field2)

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

edit

Syntax

MV_AVG(number)

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

edit

Syntax

MV_CONCAT(string,delim)

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

edit

Syntax

MV_COUNT(field)

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

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

edit

Syntax

MV_DEDUPE(field)

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

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

edit

Syntax

MV_FIRST(field)

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

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

edit

Syntax

MV_LAST(field)

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

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

edit

Syntax

MV_MAX(field)

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

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

edit

Syntax

MV_MEDIAN(number)

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

edit

Syntax

MV_MEDIAN_ABSOLUTE_DEVIATION(number)

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

edit

Syntax

MV_MIN(field)

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

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_PSERIES_WEIGHTED_SUM

edit

Syntax

MV_PSERIES_WEIGHTED_SUM(number,p)

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

edit

Syntax

MV_SLICE(field,start,end)

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

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

edit

Syntax

MV_SORT(field,order)

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

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

edit

Syntax

MV_SUM(number)

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

edit

Syntax

MV_ZIP(string1,string2,delim)

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

edit

Boolean operators for comparing against one or multiple expressions.

Binary operators

edit

Equality

edit
lhs==rhs

Check if two fields are equal. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

boolean

boolean

boolean

cartesian_point

cartesian_point

boolean

cartesian_shape

cartesian_shape

boolean

date

date

boolean

double

double

boolean

double

integer

boolean

double

long

boolean

geo_point

geo_point

boolean

geo_shape

geo_shape

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

Inequality !=

edit
lhs!=rhs

Check if two fields are unequal. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

boolean

boolean

boolean

cartesian_point

cartesian_point

boolean

cartesian_shape

cartesian_shape

boolean

date

date

boolean

double

double

boolean

double

integer

boolean

double

long

boolean

geo_point

geo_point

boolean

geo_shape

geo_shape

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

Less than <

edit
lhs<rhs

Check if one field is less than another. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

date

date

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

Less than or equal to <=

edit
lhs<=rhs

Check if one field is less than or equal to another. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

date

date

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

Greater than >

edit
lhs>rhs

Check if one field is greater than another. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

date

date

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

Greater than or equal to >=

edit
lhs>=rhs

Check if one field is greater than or equal to another. If either field is multivalued then the result is null.

This is pushed to the underlying search index if one side of the comparison is constant and the other side is a field in the index that has both an index and doc_values.

Supported types:

Supported types

lhs rhs result

date

date

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 +

edit
lhs+rhs

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 -

edit
lhs-rhs

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 *

edit
lhs*rhs

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 /

edit
lhs/rhs

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 %

edit
lhs%rhs

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

edit

The only unary operators is negation (-):

-v

Supported types:

Supported types

field result

date_period

date_period

double

double

integer

integer

long

long

time_duration

time_duration

Logical operators

edit

The following logical operators are supported:

  • AND
  • OR
  • NOT

IS NULL and IS NOT NULL predicates

edit

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 (::)

edit

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

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

edit

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

edit

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