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 functions are divided into the following categories:

ES|QL aggregate functions

edit

The STATS ... BY function supports these aggregate functions:

AVG

edit

The average of a numeric field.

FROM employees
| STATS AVG(height)
AVG(height):double

1.7682

The result is always a double not matter the input type.

COUNT

edit

Counts field values.

FROM employees
| STATS COUNT(height)
COUNT(height):long

100

Can take any field type as input and the result is always a long not matter the input type.

To count the number of rows, use 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

COUNT_DISTINCT

edit

The approximate number of distinct values.

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

7

8

Can take any field type as input and the result is always a long not matter the input type.

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.

Precision is configurable

edit

The COUNT_DISTINCT function takes an optional second parameter to configure the precision discussed previously.

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

7

9

MAX

edit

The maximum value of a numeric field.

FROM employees
| STATS MAX(languages)
MAX(languages):integer

5

MEDIAN

edit

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

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

47003

47003

Like PERCENTILE, MEDIAN is usually approximate.

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

MEDIAN_ABSOLUTE_DEVIATION

edit

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) - Xi|).

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

47003

10096.5

Like PERCENTILE, MEDIAN_ABSOLUTE_DEVIATION is usually approximate.

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

MIN

edit

The minimum value of a numeric field.

FROM employees
| STATS MIN(languages)
MIN(languages):integer

1

PERCENTILE

edit

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.

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

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.

SUM

edit

The sum of a numeric field.

FROM employees
| STATS SUM(languages)
SUM(languages):long

281

ES|QL mathematical functions

edit

ES|QL supports these mathematical functions:

ABS

edit
ABS(n)

Returns the absolute value.

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

Supported types:

n result

double

double

integer

integer

long

long

unsigned_long

unsigned_long

ACOS

edit

Syntax

ACOS(n)

Parameters

n
Numeric expression. If null, the function returns null.

Description

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

Supported types

n 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
ASIN(n)

Inverse sine trigonometric function.

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

.9

1.1197695149986342

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

ATAN

edit
ATAN(n)

Inverse tangent trigonometric function.

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

12.9

1.4934316673669235

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

ATAN2

edit
ATAN2(y,x)

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

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

12.9

0.6

1.5243181954438936

Supported types:

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

CEIL

edit
CEIL(n)

Round a number up to the nearest integer.

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

2

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

Supported types:

n result

double

double

integer

integer

long

long

unsigned_long

unsigned_long

COS

edit
COS(n)

Cosine trigonometric function.

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

1.8

-0.2272020946930871

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

COSH

edit
COSH(n)

Cosine hyperbolic function.

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

1.8

3.1074731763172667

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

E()

Euler’s number.

ROW E()
E():double

2.718281828459045

FLOOR

edit
FLOOR(n)

Round a number down to the nearest integer.

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

1

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

Supported types:

n result

double

double

integer

integer

long

long

unsigned_long

unsigned_long

LOG10

edit
LOG10(n)

Returns the log base 10. The input can be any numeric value, the return value is always a double.

Logs of negative numbers are NaN. Logs of infinites are infinite, as is the log of 0.

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

1000.0

3.0

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

PI()

The ratio of a circle’s circumference to its diameter.

ROW PI()
PI():double

3.141592653589793

POW

edit
POW(base,exponent)

Returns the value of a base (first argument) raised to the power of an exponent (second argument). Both arguments must be numeric.

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

2.0

2

4.0

Type rules

edit

The type of the returned value is determined by the types of the base and exponent. The following rules are applied to determine the result type:

  • If either of the base or exponent are of a floating point type, the result will be a double
  • Otherwise, if either the base or the exponent are 64-bit (long or unsigned long), the result will be a long
  • Otherwise, the result will be a 32-bit integer (this covers all other numeric types, including int, short and byte)

For example, using simple integers as arguments will lead to an integer result:

ROW base = 2, exponent = 2
| EVAL s = POW(base, exponent)
base:integer exponent:integer s:integer

2

2

4

The actual power function is performed using double precision values for all cases. This means that for very large non-floating point values there is a small chance that the operation can lead to slightly different answers than expected. However, a more likely outcome of very large non-floating point values is numerical overflow.

Arithmetic errors

edit

Arithmetic errors and numeric overflow do not result in an error. Instead, the result will be null and a warning for the ArithmeticException added. For example:

ROW x = POW(9223372036854775808, 2)
warning:Line 1:9: evaluation of [POW(9223372036854775808, 2)] failed, treating result as null. Only first 20 failures recorded.

warning:Line 1:9: java.lang.ArithmeticException: long overflow

x:long

null

If it is desired to protect against numerical overruns, use TO_DOUBLE on either of the arguments:

ROW x = POW(9223372036854775808, TO_DOUBLE(1))
x:double

9.223372036854776E18

Fractional exponents

edit

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

Table of supported input and output types

edit

For clarity, the following table describes the output result type for all combinations of numeric input types:

base exponent result

double

double

double

double

integer

double

integer

double

double

integer

integer

integer

long

double

double

long

integer

long

ROUND

edit

Rounds a number to the closest number with the specified number of digits. Defaults to 0 digits if no number of digits is provided. If the specified number of digits is negative, rounds to the number of digits left of the decimal point.

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

SIN

edit
SIN(n)

Sine trigonometric function.

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

1.8

0.9738476308781951

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

SINH

edit
SINH(n)

Sine hyperbolic function.

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

1.8

2.94217428809568

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

SQRT

edit
SQRT(n)

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 are NaN. Square roots of infinites are infinite.

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

100.0

10.0

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

TAN

edit
TAN(n)

Tangent trigonometric function.

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

1.8

-4.286261674628062

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

TANH

edit
TANH(n)

Tangent hyperbolic function.

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

1.8

0.9468060128462683

Supported types:

n result

double

double

integer

double

long

double

unsigned_long

double

TAU

edit
TAU()

The ratio of a circle’s circumference to its radius.

ROW TAU()
TAU():double

6.283185307179586

ES|QL string functions

edit

ES|QL supports these string functions:

CONCAT

edit

Concatenates two or more strings.

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

LEFT

edit
LEFT(string,length)

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

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

Supported types:

string length result

keyword

integer

keyword

LENGTH

edit

Returns the character length of a string.

FROM employees
| KEEP first_name, last_name, height
| EVAL fn_length = LENGTH(first_name)

LTRIM

edit

Removes leading whitespaces from strings.

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 '

REPLACE

edit

The function substitutes in the string (1st argument) any match of the regular expression (2nd argument) with the replacement string (3rd argument).

If any of the arguments are NULL, the result is NULL.

  1. This example replaces an 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

RIGHT

edit
RIGHT(string,length)

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

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

Supported types:

string length result

keyword

integer

keyword

RTRIM

edit

Removes trailing whitespaces from strings.

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'

SPLIT

edit

Split a single valued string into multiple strings. For example:

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

Which splits "foo;bar;baz;qux;quux;corge" on ; and returns an array:

words:keyword word:keyword

foo;bar;baz;qux;quux;corge

[foo,bar,baz,qux,quux,corge]

Only single byte delimiters are currently supported.

SUBSTRING

edit

Returns a substring of a string, specified by a start position and an optional length. 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

TRIM

edit
TRIM(arg1)

Removes leading and trailing whitespaces from strings.

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

some text

red

Supported types:

arg1 result

keyword

keyword

text

text

ES|QL date-time functions

edit

ES|QL supports these date-time functions:

AUTO_BUCKET

edit

Creates human-friendly buckets and returns a datetime value for each row that corresponds to the resulting bucket the row falls into. Combine AUTO_BUCKET with STATS ... BY to create a date histogram.

You provide a target number of buckets, a start date, and an end date, and it picks an appropriate bucket size to generate the target number of buckets or fewer. For example, this asks for at most 20 buckets over a whole year, which picks monthly buckets:

ROW date=TO_DATETIME("1985-07-09T00:00:00.000Z")
| EVAL bucket=AUTO_BUCKET(date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
date:datetime bucket:datetime

1985-07-09T00:00:00.000Z

1985-07-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.

If you ask for more buckets then AUTO_BUCKET can pick a smaller range. For example, asking for at most 100 buckets in a year will get you week long buckets:

ROW date=TO_DATETIME("1985-07-09T00:00:00.000Z")
| EVAL bucket=AUTO_BUCKET(date, 100, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
date:datetime bucket:datetime

1985-07-09T00:00:00.000Z

1985-07-08T00:00:00.000Z

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

A more complete example might look like:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| EVAL bucket = AUTO_BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| STATS AVG(salary) BY bucket
| 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

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

Numeric fields

edit

auto_bucket can also operate on numeric fields like this:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| EVAL bs = AUTO_BUCKET(salary, 20, 25324, 74999)
| SORT hire_date, salary
| KEEP hire_date, salary, bs
hire_date:date salary:integer bs:double

1985-02-18T00:00:00.000Z

66174

65000.0

1985-02-24T00:00:00.000Z

26436

25000.0

1985-05-13T00:00:00.000Z

44817

40000.0

1985-07-09T00:00:00.000Z

62405

60000.0

1985-09-17T00:00:00.000Z

49095

45000.0

1985-10-14T00:00:00.000Z

54329

50000.0

1985-10-20T00:00:00.000Z

48735

45000.0

1985-11-19T00:00:00.000Z

52833

50000.0

1985-11-20T00:00:00.000Z

33956

30000.0

1985-11-20T00:00:00.000Z

74999

70000.0

1985-11-21T00:00:00.000Z

56371

55000.0

Unlike the example above where you are intentionally filtering on a date range, you rarely want to filter on a numeric range. So you have find the min and max separately. We don’t yet have an easy way to do that automatically. Improvements coming!

DATE_EXTRACT

edit

Extracts parts of a date, like year, month, day, hour. The supported field types are those provided by java.time.temporal.ChronoField.

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

DATE_FORMAT

edit

Returns a string representation of a date in the provided format. If no format is specified, the yyyy-MM-dd'T'HH:mm:ss.SSSZ format is used.

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

DATE_PARSE

edit

Syntax

DATE_PARSE([format,] date_string)

Parameters

format
The date format. Refer to the DateTimeFormatter documentation for the syntax. If null, the function returns null.
date_string
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.

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

Rounds down a date to the closest interval. Intervals can be expressed using the timespan literal syntax.

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

NOW

edit

Returns current date and time.

ROW current_date = NOW()

ES|QL type conversion functions

edit

ES|QL supports these type conversion functions:

TO_BOOLEAN

edit

Converts an input value to a boolean value.

The input can be a single- or multi-valued field or an expression. The input type must be of a string or numeric type.

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. For 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]

The numerical value of 0 will be converted to false, anything else will be converted to true.

Alias: TO_BOOL

TO_DATETIME

edit

Converts an input value to a date value.

The input can be a single- or multi-valued field or an expression. The input type must be of a string or numeric type.

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). For example:

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]

Alias: TO_DT

TO_DEGREES

edit

Converts a number in radians to degrees.

The input can be a single- or multi-valued field or an expression. The input type must be of a numeric type and result is always 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

Converts an input value to a double value.

The input can be a single- or multi-valued field or an expression. The input type must be of a boolean, date, string or numeric type.

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

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.

Alias: TO_DBL

TO_INTEGER

edit

Converts an input value to an integer value.

The input can be a single- or multi-valued field or an expression. The input type must be of a boolean, date, string or numeric type.

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.ql.QlIllegalArgumentException: [501379200000] out of [integer] range"

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.

Alias: TO_INT

TO_IP

edit

Converts an input string to an IP value.

The input can be a single- or multi-valued field or an expression.

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 the example above 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

Converts an input value to a long value.

The input can be a single- or multi-valued field or an expression. The input type must be of a boolean, date, string or numeric type.

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

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.

TO_RADIANS

edit

Converts a number in degrees to radians.

The input can be a single- or multi-valued field or an expression. The input type must be of a numeric type and result is always 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
TO_STRING(v)

Converts a field into a string. For example:

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

Alias: TO_STR

Supported types:

v result

boolean

keyword

datetime

keyword

double

keyword

integer

keyword

ip

keyword

keyword

keyword

long

keyword

text

keyword

unsigned_long

keyword

version

keyword

TO_UNSIGNED_LONG

edit

Converts an input value to an unsigned long value.

The input can be a single- or multi-valued field or an expression. The input type must be of a boolean, date, string or numeric type.

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

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.

Alias: TO_ULONG, TO_UL

TO_VERSION

edit
TO_VERSION(v)

Converts an input string to a version value. For example:

ROW v = TO_VERSION("1.2.3")
v:version

1.2.3

The input can be a single- or multi-valued field or an expression.

Alias: TO_VER

Supported types:

v result

keyword

version

text

version

version

version

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(condition1, value1[, ..., conditionN, valueN][, default_value])

Parameters

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

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.

Example

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

COALESCE

edit

Returns the first non-null value.

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

null

b

b

GREATEST

edit
GREATEST(first,rest)

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

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

10

20

20

When run on keyword or text fields, this’ll return 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

double

double

double

integer

integer

integer

ip

ip

ip

keyword

keyword

keyword

long

long

long

text

text

text

version

version

version

LEAST

edit
LEAST(first,rest)

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

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

10

20

10

When run on keyword or text fields, this’ll return the first string in alphabetical order. When run on boolean columns this will return false if any values are false.

Supported types:

first rest result

boolean

boolean

boolean

double

double

double

integer

integer

integer

ip

ip

ip

keyword

keyword

keyword

long

long

long

text

text

text

version

version

version

ES|QL multivalue functions

edit

ES|QL supports these multivalue functions:

MV_AVG

edit

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

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

[3, 5, 1, 6]

3.75

The output type is always a double and the input type can be any number.

MV_CONCAT

edit

Converts a multivalued string field into a single valued field containing the concatenation of all values separated by a delimiter:

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

["foo", "zoo", "bar"]

"foo, zoo, bar"

If you want to concat non-string fields call TO_STRING on them 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

Converts a multivalued field into a single valued field containing a count of the number of values:

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

["foo", "zoo", "bar"]

3

This function accepts all types and always returns an integer.

MV_DEDUPE

edit

Removes duplicates from a multivalued field. For 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_DEDUPE may, but won’t always, sort the values in the field.

MV_MAX

edit

Converts a multivalued field into a single valued field containing the maximum value. For example:

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 field type, including keyword fields. In that case 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

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

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

[3, 5, 1]

3

It can be used by any numeric field type and returns a value of the same type. 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 field is not floating point then 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_MIN

edit

Converts a multivalued field into a single valued field containing the minimum value. For example:

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

[2, 1]

1

It can be used by any field type, including keyword fields. In that case 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_SUM

edit

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

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

[3, 5, 6]

14

The input type can be any number and the output type is the same as the input type.

ES|QL operators

edit

Boolean operators for comparing against one or multiple expressions.

Binary operators

edit

These binary comparison operators are supported:

  • equality: ==
  • inequality: !=
  • less than: <
  • less than or equal: <=
  • larger than: >
  • larger than or equal: >=

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

CIDR_MATCH

edit

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

CIDR_MATCH accepts two or more arguments. The first argument is the IP address of type ip (both IPv4 and IPv6 are supported). Subsequent arguments are the CIDR blocks to test the IP against.

FROM hosts
| WHERE CIDR_MATCH(ip, "127.0.0.2/32", "127.0.0.3/32")

ENDS_WITH

edit
ENDS_WITH(arg1,arg2)

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

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

Supported types:

arg1 arg2 result

keyword

keyword

boolean

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

IS_FINITE

edit

Returns a boolean that indicates whether its input is a finite number.

ROW d = 1.0
| EVAL s = IS_FINITE(d/0)

IS_INFINITE

edit

Returns a boolean that indicates whether its input is infinite.

ROW d = 1.0
| EVAL s = IS_INFINITE(d/0)

IS_NAN

edit

Returns a boolean that indicates whether its input is not a number.

ROW d = 1.0
| EVAL s = IS_NAN(d)

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.
FROM employees
| WHERE first_name LIKE "?b*"
| KEEP first_name, last_name
first_name:keyword last_name:keyword

Ebbe

Callaway

Eberhardt

Terkki

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.

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

Alejandro

McAlpine

STARTS_WITH

edit
STARTS_WITH(arg1,arg2)

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

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

Supported types:

arg1 arg2 result

keyword

keyword

boolean