ES|QL functions and operators
editES|QL functions and operators
editES|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
editThe STATS ... BY
function supports these aggregate functions:
AVG
editSyntax
AVG(column)
-
column
-
Numeric column. If
null
, the function returnsnull
.
Description
The average of a numeric field.
Supported types
The result is always a double
no matter the input type.
Example
FROM employees | STATS AVG(height)
AVG(height):double |
---|
1.7682 |
COUNT
editSyntax
COUNT([input])
Parameters
-
input
- Column or literal for which to count the number of values. If omitted, returns a count all (the number of rows).
Description
Returns the total number (count) of input values.
Supported types
Can take any field type as input.
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 |
COUNT_DISTINCT
editSyntax
COUNT_DISTINCT(column[, precision])
Parameters
-
column
- Column for which to count the number of distinct values.
-
precision
- Precision. Refer to Counts are approximate.
Description
Returns the approximate number of distinct values.
Counts are approximate
editComputing exact counts requires loading values into a set and returning its size. This doesn’t scale when working on high-cardinality sets and/or large values as the required memory usage and the need to communicate those per-shard sets between nodes would utilize too many resources of the cluster.
This COUNT_DISTINCT
function is based on the
HyperLogLog++
algorithm, which counts based on the hashes of the values with some interesting
properties:
- configurable precision, which decides on how to trade memory for accuracy,
- excellent accuracy on low-cardinality sets,
- fixed memory usage: no matter if there are tens or billions of unique values, memory usage only depends on the configured precision.
For a precision threshold of c
, the implementation that we are using requires
about c * 8
bytes.
The following chart shows how the error varies before and after the threshold:
For all 3 thresholds, counts have been accurate up to the configured threshold. Although not guaranteed, this is likely to be the case. Accuracy in practice depends on the dataset in question. In general, most datasets show consistently good accuracy. Also note that even with a threshold as low as 100, the error remains very low (1-6% as seen in the above graph) even when counting millions of items.
The HyperLogLog++ algorithm depends on the leading zeros of hashed values, the exact distributions of hashes in a dataset can affect the accuracy of the cardinality.
The COUNT_DISTINCT
function takes an optional second parameter to configure the
precision.
Supported types
Can take any field type as input.
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:
FROM hosts | STATS COUNT_DISTINCT(ip0, 80000), COUNT_DISTINCT(ip1, 5)
COUNT_DISTINCT(ip0,80000):long | COUNT_DISTINCT(ip1,5):long |
---|---|
7 |
9 |
MAX
editThe maximum value of a numeric field.
FROM employees | STATS MAX(languages)
MAX(languages):integer |
---|
5 |
MEDIAN
editThe 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
editThe 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
editThe minimum value of a numeric field.
FROM employees | STATS MIN(languages)
MIN(languages):integer |
---|
1 |
PERCENTILE
editThe 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
editThere are many different algorithms to calculate percentiles. The naive
implementation simply stores all the values in a sorted array. To find the 50th
percentile, you simply find the value that is at my_array[count(my_array) * 0.5]
.
Clearly, the naive implementation does not scale — the sorted array grows linearly with the number of values in your dataset. To calculate percentiles across potentially billions of values in an Elasticsearch cluster, approximate percentiles are calculated.
The algorithm used by the percentile
metric is called TDigest (introduced by
Ted Dunning in
Computing Accurate Quantiles using T-Digests).
When using this metric, there are a few guidelines to keep in mind:
-
Accuracy is proportional to
q(1-q)
. This means that extreme percentiles (e.g. 99%) are more accurate than less extreme percentiles, such as the median - For small sets of values, percentiles are highly accurate (and potentially 100% accurate if the data is small enough).
- As the quantity of values in a bucket grows, the algorithm begins to approximate the percentiles. It is effectively trading accuracy for memory savings. The exact level of inaccuracy is difficult to generalize, since it depends on your data distribution and volume of data being aggregated
The following chart shows the relative error on a uniform distribution depending on the number of collected values and the requested percentile:
It shows how precision is better for extreme percentiles. The reason why error diminishes for large number of values is that the law of large numbers makes the distribution of values more and more uniform and the t-digest tree can do a better job at summarizing it. It would not be the case on more skewed distributions.
PERCENTILE
is also non-deterministic.
This means you can get slightly different results using the same data.
SUM
editThe sum of a numeric field.
FROM employees | STATS SUM(languages)
SUM(languages):long |
---|
281 |
ES|QL mathematical functions
editES|QL supports these mathematical functions:
ABS
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the absolute value.
Supported types
n | 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
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
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
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the arcsine of the input numeric expression as an angle, expressed in radians.
Supported types
n | 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
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the arctangent of the input numeric expression as an angle, expressed in radians.
Supported types
n | 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
editSyntax
Parameters
-
y
-
Numeric expression. If
null
, the function returnsnull
. -
x
-
Numeric expression. If
null
, the function returnsnull
.
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 | 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 |
Example
ROW y=12.9, x=.6 | EVAL atan2=ATAN2(y, x)
y:double | x:double | atan2:double |
---|---|---|
12.9 |
0.6 |
1.5243181954438936 |
CEIL
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
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
n | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Example
ROW a=1.8 | EVAL a=CEIL(a)
a:double |
---|
2 |
COS
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the cosine of n
. Input
expected in radians.
Supported types
n | 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
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Supported types
n | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Description
Returns the hyperbolic cosine.
Example
ROW a=1.8 | EVAL cosh=COSH(a)
a:double | cosh:double |
---|---|
1.8 |
3.1074731763172667 |
E
editROW E()
E():double |
---|
2.718281828459045 |
FLOOR
editRound 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
editReturns 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
editThe ratio of a circle’s circumference to its diameter.
ROW PI()
PI():double |
---|
3.141592653589793 |
POW
editReturns the value of a base (first argument) raised to the power of an exponent (second argument). 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.
ROW base = 2.0, exponent = 2 | EVAL result = POW(base, exponent)
base:double | exponent:integer | result:double |
---|---|---|
2.0 |
2 |
4.0 |
Fractional exponents
editThe 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
editFor 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 |
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 |
ROUND
editRounds 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
editSine 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
editSine 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
editReturns 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
editTangent 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
editTangent 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
editThe ratio of a circle’s circumference to its radius.
ROW TAU()
TAU():double |
---|
6.283185307179586 |
ES|QL string functions
editES|QL supports these string functions:
CONCAT
editSyntax
CONCAT(string1, string2[, ..., stringN])
Parameters
-
stringX
- Strings to concatenate.
Description
Concatenates two or more strings.
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 |
LEFT
editReturn 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
editReturns the character length of a string.
FROM employees | KEEP first_name, last_name, height | EVAL fn_length = LENGTH(first_name)
LTRIM
editRemoves 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 ' |
Supported types:
str | result |
---|---|
keyword |
keyword |
text |
text |
REPLACE
editThe 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
.
- 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
editReturn 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
editRemoves 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' |
Supported types:
str | result |
---|---|
keyword |
keyword |
text |
text |
SPLIT
editSplit 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
editReturns 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
editRemoves 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:
str | result |
---|---|
keyword |
keyword |
text |
text |
ES|QL date-time functions
editES|QL supports these date-time functions:
AUTO_BUCKET
editSyntax
AUTO_BUCKET(field, buckets, from, to)
Parameters
-
field
- Numeric or date column from which to derive buckets.
-
buckets
- Target number of buckets.
-
from
- Start of the range. Can be a number or a date expressed as a string.
-
to
- End of the range. Can be a number or a date expressed as a string.
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
hire_date:date | month:date |
---|---|
1985-02-18T00:00:00.000Z |
1985-02-01T00: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-01T00:00:00.000Z |
1985-10-20T00:00:00.000Z |
1985-10-01T00:00:00.000Z |
1985-11-19T00:00:00.000Z |
1985-11-01T00:00:00.000Z |
1985-11-20T00:00:00.000Z |
1985-11-01T00:00:00.000Z |
1985-11-20T00:00:00.000Z |
1985-11-01T00: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 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
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 |
AUTO_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" | 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
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 |
AUTO_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
AUTO_BUCKET
with WHERE
to filter rows.
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
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.
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, DATE_FORMAT(NOW() - 1 day), DATE_FORMAT(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
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 |
DATE_EXTRACT
editSyntax
DATE_EXTRACT(date_part, date)
Parameters
-
date_part
-
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
, oryear_of_era
. Refer to java.time.temporal.ChronoField for a description of these values.If
null
, the function returnsnull
. -
date
-
Date expression. If
null
, the function returnsnull
.
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)
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 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
@timestamp:date | client_ip:ip | event_duration:long | message:keyword |
---|
DATE_FORMAT
editSyntax
DATE_FORMAT([format,] date)
Parameters
-
format
-
Date format (optional). If no format is specified, the
yyyy-MM-dd'T'HH:mm:ss.SSSZ
format is used. Ifnull
, the function returnsnull
. -
date
-
Date expression. If
null
, the function returnsnull
.
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)
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
editSyntax
DATE_PARSE([format,] date_string)
Parameters
-
format
-
The date format. Refer to the
DateTimeFormatter
documentation for the syntax. Ifnull
, the function returnsnull
. -
date_string
-
Date expression as a string. If
null
or an empty string, the function returnsnull
.
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
editSyntax
DATE_TRUNC(interval, date)
Parameters
-
interval
-
Interval, expressed using the timespan literal
syntax. If
null
, the function returnsnull
. -
date
-
Date expression. If
null
, the function returnsnull
.
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)
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
editReturns current date and time.
ROW current_date = NOW()
ES|QL type conversion functions
editES|QL supports these type conversion functions:
TO_BOOLEAN
editConverts 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_CARTESIANPOINT
editConverts an input value to a point
value.
The input can be a single- or multi-valued field or an expression.
The input type must be a string or a cartesian point
.
A string will only be successfully converted if it respects the WKT Point format:
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_DATETIME
editConverts 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
editConverts 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
editConverts 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_GEOPOINT
editConverts an input value to a geo_point
value.
The input can be a single- or multi-valued field or an expression.
The input type must be a string or a geo_point
.
A string will only be successfully converted if it respects the WKT Point format:
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_INTEGER
editConverts 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.InvalidArgumentException: [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
editConverts 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
editConverts 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
editConverts 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
editConverts 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 |
cartesian_point |
keyword |
datetime |
keyword |
double |
keyword |
geo_point |
keyword |
integer |
keyword |
ip |
keyword |
keyword |
keyword |
long |
keyword |
text |
keyword |
unsigned_long |
keyword |
version |
keyword |
TO_UNSIGNED_LONG
editConverts 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
editConverts 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
editConditional functions return one of their arguments by evaluating in an if-else manner. ES|QL supports these conditional functions:
CASE
editSyntax
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. 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
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
editSyntax
COALESCE(expression1 [, ..., expressionN])
Parameters
-
expressionX
- Expression to evaluate.
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)
a:null | b:keyword | COALESCE(a,b):keyword |
---|---|---|
null |
b |
b |
GREATEST
editReturns 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
editReturns 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
editES|QL supports these multivalue functions:
MV_AVG
editConverts 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
editConverts 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" |
Supported types:
v | delim | result |
---|---|---|
keyword |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
keyword |
text |
text |
keyword |
MV_COUNT
editConverts 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 |
Supported types:
v | result |
---|---|
boolean |
integer |
cartesian_point |
integer |
datetime |
integer |
double |
integer |
geo_point |
integer |
integer |
integer |
ip |
integer |
keyword |
integer |
long |
integer |
text |
integer |
unsigned_long |
integer |
version |
integer |
MV_DEDUPE
editRemoves 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"] |
Supported types:
v | result |
---|---|
boolean |
boolean |
datetime |
datetime |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
text |
version |
version |
MV_DEDUPE
may, but won’t always, sort the values in the field.
MV_MAX
editConverts 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" |
Supported types:
v | result |
---|---|
boolean |
boolean |
datetime |
datetime |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
text |
unsigned_long |
unsigned_long |
version |
version |
MV_MEDIAN
editConverts 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
editConverts 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" |
Supported types:
v | result |
---|---|
boolean |
boolean |
datetime |
datetime |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
text |
unsigned_long |
unsigned_long |
version |
version |
MV_SUM
editConverts 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
editBoolean operators for comparing against one or multiple expressions.
Binary operators
editThese binary comparison operators are supported:
-
equality:
==
-
inequality:
!=
-
less than:
<
-
less than or equal:
<=
-
larger than:
>
-
larger than or equal:
>=
Logical operators
editThe following logical operators are supported:
-
AND
-
OR
-
NOT
IS NULL
and IS NOT NULL
predicates
editFor 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
editSyntax
CIDR_MATCH(ip, block1[, ..., blockN])
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.
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 |
ENDS_WITH
editReturns 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 |
IN
editThe 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
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns a boolean that indicates whether its input is a finite number.
Supported types
n | result |
---|---|
double |
boolean |
Example
ROW d = 1.0 | EVAL s = IS_FINITE(d/0)
IS_INFINITE
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns a boolean that indicates whether its input is an infinite number.
Supported types
n | result |
---|---|
double |
boolean |
Example
ROW d = 1.0 | EVAL s = IS_INFINITE(d/0)
IS_NAN
editSyntax
Parameters
-
n
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns a boolean that indicates whether its input is Not-a-Number (NaN).
Supported types
n | result |
---|---|
double |
boolean |
Example
ROW d = 1.0 | EVAL s = IS_NAN(d)
LIKE
editUse 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
editUse 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
editReturns 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 |