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

AVGedit

Syntax

AVG(expression)

Description

The average of a numeric expression.

Supported types

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

Examples

FROM employees
| STATS AVG(height)

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 = AVG(MV_AVG(salary_change))

COUNTedit

Syntax

COUNT([expression])

Parameters

Description

Returns the total number (count) of input values.

Supported types

Can take any field type as input.

Examples

FROM employees
| STATS COUNT(height)

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

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

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, ";"))

COUNT_DISTINCTedit

Syntax

COUNT_DISTINCT(expression[, precision_threshold])

Parameters

Description

Returns the approximate number of distinct values.

Supported types

Can take any field type as input.

Examples

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

With the optional second parameter to configure the precision threshold:

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

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, ";"))

Counts are approximateedit

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:

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

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

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

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

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

MAXedit

Syntax

MAX(expression)

Parameters

Description

Returns the maximum value of a numeric expression.

Example

FROM employees
| STATS MAX(languages)

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

MEDIANedit

Syntax

MEDIAN(expression)

Parameters

Description

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

Example

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

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_ABSOLUTE_DEVIATIONedit

Syntax

MEDIAN_ABSOLUTE_DEVIATION(expression)

Parameters

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

Example

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

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

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

MINedit

Syntax

MIN(expression)

Parameters

Description

Returns the minimum value of a numeric expression.

Example

FROM employees
| STATS MIN(languages)

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

PERCENTILEedit

Syntax

PERCENTILE(expression, percentile)

Parameters

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.

Example

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

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)

PERCENTILE is (usually) approximateedit

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:

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

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

ST_CENTROIDedit

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

FROM airports
| STATS centroid=ST_CENTROID(location)

Supported types:

SUMedit

Syntax

SUM(expression)

Description

Returns the sum of a numeric expression.

Example

FROM employees
| STATS SUM(languages)

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

ES|QL supports these mathematical functions:

ABSedit

Syntax

Parameters

Description

Returns the absolute value.

Supported types

Examples

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

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

ACOSedit

Syntax

Parameters

Description

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

Supported types

Example

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

ASINedit

Syntax

Parameters

Description

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

Supported types

Example

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

ATANedit

Syntax

Parameters

Description

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

Supported types

Example

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

ATAN2edit

Syntax

Parameters

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

Example

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

CEILedit

Syntax

Parameters

Description

Round a number up to the nearest integer.

Supported types

Example

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

COSedit

Syntax

Parameters

Description

Returns the cosine of n. Input expected in radians.

Supported types

Example

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

COSHedit

Syntax

Parameters

Description

Returns the hyperbolic cosine.

Supported types

Example

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

Eedit

Syntax

Description

Returns Euler’s number.

Example

ROW E()

FLOORedit

Syntax

Parameters

Description

Rounds a number down to the nearest integer.

Supported types

Example

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

LOGedit

Syntax

LOG([base,] value)

Parameters

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, infinites and base of one return null as well as a warning.

Supported types

Example

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

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

LOG10edit

Syntax

Description

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

Logs of 0, negative numbers, and infinites return null as well as a warning.

Supported types

Example

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

PIedit

Syntax

Description

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

Example

ROW PI()

POWedit

Syntax

Parameters

Description

Returns the value of base raised to the power of exponent. Both arguments must be numeric. The output is always a double. Note that it is still possible to overflow a double result here; in that case, null will be returned.

Supported types

Examples

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

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)

ROUNDedit

Syntax

Parameters

Description

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.

Supported types

Example

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

SINedit

Syntax

Parameters

Description

Sine trigonometric function. Input expected in radians.

Supported types

Example

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

SINHedit

Syntax

Parameters

Description

Sine hyperbolic function.

Supported types

Example

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

SQRTedit

Syntax

Parameters

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

Supported types

Example

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

TANedit

Syntax

Parameters

Description

Tangent trigonometric function. Input expected in radians.

Supported types

Example

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

TANHedit

Syntax

Parameters

Description

Tangent hyperbolic function.

Supported types

Example

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

TAUedit

Syntax

Description

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

Example

ROW TAU()

ES|QL supports these string functions:

CONCATedit

Syntax

CONCAT(string1, string2[, ..., stringN])

Parameters

Description

Concatenates two or more strings.

Example

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

LEFTedit

Syntax

Parameters

Description

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

Supported types

Example

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

LENGTHedit

Syntax

LENGTH(str)

Parameters

Description

Returns the character length of a string.

Example

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

LTRIMedit

Syntax

Parameters

Description

Removes leading whitespaces from strings.

Supported types

Example

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

REPLACEedit

Syntax

Parameters

Description

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

If any of the arguments is null, the result is null.

Supported types

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

RIGHTedit

Syntax

Parameters

Description

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

Supported types

Example

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

RTRIMedit

Syntax

Parameters

Description

Removes trailing whitespaces from strings.

Supported types

Example

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

SPLITedit

Parameters

Description

Splits a single valued string into multiple strings.

Supported types

Example

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

SUBSTRINGedit

Syntax

Parameters

Description

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

Supported types

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)

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)

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)

TO_LOWERedit

Syntax

Parameters

Description

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

Supported types

Example

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

TO_UPPERedit

Syntax

Parameters

Description

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

Supported types

Example

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

TRIMedit

Syntax

Parameters

Description

Removes leading and trailing whitespaces from strings.

Supported types

Example

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

ES|QL supports these date-time functions:

AUTO_BUCKETedit

Syntax

AUTO_BUCKET(expression, buckets, from, to)

Parameters

Description

Creates human-friendly buckets and returns a value for each row that corresponds to the resulting bucket the row falls into.

Using a target number of buckets, a start of a range, and an end of a range, AUTO_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"
| EVAL month = AUTO_BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| KEEP hire_date, month
| SORT hire_date

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 AUTO_BUCKET with STATS ... BY to create a histogram:

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

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"
| EVAL week = AUTO_BUCKET(hire_date, 100, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z")
| STATS hires_per_week = COUNT(*) BY week
| SORT week

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

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

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.

Examples

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()
| EVAL bucket = AUTO_BUCKET(@timestamp, 25, NOW() - 1 day, NOW())
| STATS COUNT(*) BY bucket

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

DATE_DIFFedit

Syntax

Parameters

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.

Supported types

Example

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)

DATE_EXTRACTedit

Syntax

DATE_EXTRACT(date_part, date)

Parameters

Description

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

Examples

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

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

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

DATE_FORMATedit

Syntax

DATE_FORMAT([format,] date)

Parameters

Description

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

Example

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

DATE_PARSEedit

Syntax

DATE_PARSE([format,] date_string)

Parameters

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_TRUNCedit

Syntax

DATE_TRUNC(interval, date)

Parameters

Description

Rounds down a date to the closest interval.

Examples

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

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

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

NOWedit

Syntax

NOW()

Description

Returns current date and time.

Example

ROW current_date = NOW()

To retrieve logs from the last hour:

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

ES|QL supports these type conversion functions:

TO_BOOLEANedit

Alias

TO_BOOL

Syntax

TO_BOOLEAN(v)

Parameters

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

The input type must be of a string or numeric type.

Example

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

TO_CARTESIANPOINTedit

Syntax

TO_CARTESIANPOINT(v)

Parameters

Description

Converts an input value to a point value.

A string will only be successfully converted if it respects the WKT Point format.

Supported types

Example

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

TO_CARTESIANSHAPEedit

Syntax

TO_CARTESIANSHAPE(v)

Parameters

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

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)

TO_DATETIMEedit

Alias

TO_DT

Syntax

TO_DATETIME(v)

Parameters

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.

Supported types

The input type must be of a string or numeric type.

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)

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)

TO_DEGREESedit

Syntax

TO_DEGREES(v)

Parameters

Description

Converts a number in radians to degrees.

Supported types

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)

TO_DOUBLEedit

Alias

TO_DBL

Syntax

TO_DOUBLE(v)

Parameters

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

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)

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_GEOPOINTedit

Syntax

TO_GEOPOINT(v)

Parameters

Description

Converts an input value to a geo_point value.

Supported types

A string will only be successfully converted if it respects the WKT Point format.

Example

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

TO_GEOSHAPEedit

Syntax

TO_GEOPOINT(v)

Parameters

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

Example

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

TO_INTEGERedit

Alias

TO_INT

Syntax

TO_INTEGER(v)

Parameters

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

The input type must be of a boolean, date, string or numeric type.

Example

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

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

TO_IPedit

Syntax

TO_IP(v)

Parameters

Description

Converts an input string to an IP value.

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

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_LONGedit

Syntax

TO_LONG(v)

Parameters

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

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)

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_RADIANSedit

Syntax

TO_RADIANS(v)

Parameters

Description

Converts a number in degrees to radians.

Supported types

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)

TO_STRINGedit

Alias

TO_STR

Parameters

Description

Converts an input value into a string.

Supported types

Example

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

It also works fine on multivalued fields:

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

TO_UNSIGNED_LONGedit

Aliases

TO_ULONG, TO_UL

Syntax

TO_UNSIGNED_LONG(v)

Parameters

Description

Converts an input value to an unsigned long value.

Supported types

The input type must be of a boolean, date, string or numeric type.

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.

Example

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

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_VERSIONedit

Alias

TO_VER

Syntax

Parameters

Description

Converts an input string to a version value.

Supported types

Example

ROW v = TO_VERSION("1.2.3")

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

CASEedit

Syntax

CASE(condition1, value1[, ..., conditionN, valueN][, default_value])

Parameters

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.

Example

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

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)

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

COALESCEedit

Syntax

COALESCE(expression1 [, ..., expressionN])

Parameters

Description

Returns the first of its arguments that is not null. If all arguments are null, it returns null.

Example

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

GREATESTedit

Syntax

Parameters

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.

Supported types

Example

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

LEASTedit

Syntax

Parameters

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

Example

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

ES|QL supports these multivalue functions:

MV_AVGedit

Syntax

MV_AVG(expression)

Parameters

Description

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

Supported types

Example

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

MV_CONCATedit

Syntax

Parameters

Description

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

Supported types

Examples

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

To concat non-string columns, call TO_STRING first:

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

MV_COUNTedit

Syntax

Parameters

Description

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

Supported types

Example

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

MV_DEDUPEedit

Syntax

Parameters

Description

Removes duplicates from a multivalue expression.

Supported types

Example

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

MV_FIRSTedit

Syntax

Parameters

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