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 reference documentation is divided into the following categories:
Functions overview
editAggregate functions
-
AVG
-
COUNT
-
COUNT_DISTINCT
-
MAX
-
MEDIAN
-
MEDIAN_ABSOLUTE_DEVIATION
-
MIN
-
PERCENTILE
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_CENTROID_AGG
-
SUM
-
TOP
-
VALUES
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
WEIGHTED_AVG
Grouping functions
Date and time functions
IP functions
Math functions
Search functions
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
MATCH
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
QSTR
Spatial functions
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_INTERSECTS
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_DISJOINT
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_CONTAINS
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_WITHIN
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_X
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_Y
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_DISTANCE
String functions
Type conversion functions
-
TO_BOOLEAN
-
TO_CARTESIANPOINT
-
TO_CARTESIANSHAPE
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_DATEPERIOD
-
TO_DATETIME
-
TO_DEGREES
-
TO_DOUBLE
-
TO_GEOPOINT
-
TO_GEOSHAPE
-
TO_INTEGER
-
TO_IP
-
TO_LONG
-
TO_RADIANS
-
TO_STRING
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_TIMEDURATION
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_UNSIGNED_LONG
-
TO_VERSION
Operators overview
editES|QL aggregate functions
editThe STATS ... BY
command supports these aggregate functions:
-
AVG
-
COUNT
-
COUNT_DISTINCT
-
MAX
-
MEDIAN
-
MEDIAN_ABSOLUTE_DEVIATION
-
MIN
-
PERCENTILE
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_CENTROID_AGG
-
SUM
-
TOP
-
VALUES
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
WEIGHTED_AVG
AVG
editSyntax
Parameters
-
number
Description
The average of a numeric field.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
Examples
FROM employees | STATS AVG(height)
AVG(height):double |
---|
1.7682 |
The expression can use inline functions. For example, to calculate the average over a multivalued column, first use MV_AVG
to average the multiple values per row, and use the result with the AVG
function
FROM employees | STATS avg_salary_change = ROUND(AVG(MV_AVG(salary_change)), 10)
avg_salary_change:double |
---|
1.3904535865 |
COUNT
editSyntax
Parameters
-
field
-
Expression that outputs values to be counted. If omitted, equivalent to
COUNT(*)
(the number of rows).
Description
Returns the total number (count) of input values.
Supported types
field | result |
---|---|
boolean |
long |
cartesian_point |
long |
date |
long |
double |
long |
geo_point |
long |
integer |
long |
ip |
long |
keyword |
long |
long |
long |
text |
long |
unsigned_long |
long |
version |
long |
Examples
FROM employees | STATS COUNT(height)
COUNT(height):long |
---|
100 |
To count the number of rows, use COUNT()
or COUNT(*)
FROM employees | STATS count = COUNT(*) BY languages | SORT languages DESC
count:long | languages:integer |
---|---|
10 |
null |
21 |
5 |
18 |
4 |
17 |
3 |
19 |
2 |
15 |
1 |
The expression can use inline functions. This example splits a string into multiple values using the SPLIT
function and counts the values
ROW words="foo;bar;baz;qux;quux;foo" | STATS word_count = COUNT(SPLIT(words, ";"))
word_count:long |
---|
6 |
To count the number of times an expression returns TRUE
use a WHERE
command to remove rows that shouldn’t be included
ROW n=1 | WHERE n < 0 | STATS COUNT(n)
COUNT(n):long |
---|
0 |
To count the same stream of data based on two different expressions use the pattern COUNT(<expression> OR NULL)
ROW n=1 | STATS COUNT(n > 0 OR NULL), COUNT(n < 0 OR NULL)
COUNT(n > 0 OR NULL):long | COUNT(n < 0 OR NULL):long |
---|---|
1 |
0 |
COUNT_DISTINCT
editSyntax
Parameters
-
field
- Column or literal for which to count the number of distinct values.
-
precision
- Precision threshold. Refer to Counts are approximate. The maximum supported value is 40000. Thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.
Description
Returns the approximate number of distinct values.
Supported types
field | precision | result |
---|---|---|
boolean |
integer |
long |
boolean |
long |
long |
boolean |
unsigned_long |
long |
boolean |
long |
|
date |
integer |
long |
date |
long |
long |
date |
unsigned_long |
long |
date |
long |
|
double |
integer |
long |
double |
long |
long |
double |
unsigned_long |
long |
double |
long |
|
integer |
integer |
long |
integer |
long |
long |
integer |
unsigned_long |
long |
integer |
long |
|
ip |
integer |
long |
ip |
long |
long |
ip |
unsigned_long |
long |
ip |
long |
|
keyword |
integer |
long |
keyword |
long |
long |
keyword |
unsigned_long |
long |
keyword |
long |
|
long |
integer |
long |
long |
long |
long |
long |
unsigned_long |
long |
long |
long |
|
text |
integer |
long |
text |
long |
long |
text |
unsigned_long |
long |
text |
long |
|
version |
integer |
long |
version |
long |
long |
version |
unsigned_long |
long |
version |
long |
Examples
FROM hosts | STATS COUNT_DISTINCT(ip0), COUNT_DISTINCT(ip1)
COUNT_DISTINCT(ip0):long | COUNT_DISTINCT(ip1):long |
---|---|
7 |
8 |
With the optional second parameter to configure the precision threshold
FROM hosts | STATS COUNT_DISTINCT(ip0, 80000), COUNT_DISTINCT(ip1, 5)
COUNT_DISTINCT(ip0, 80000):long | COUNT_DISTINCT(ip1, 5):long |
---|---|
7 |
9 |
The expression can use inline functions. This example splits a string into multiple values using the SPLIT
function and counts the unique values
ROW words="foo;bar;baz;qux;quux;foo" | STATS distinct_word_count = COUNT_DISTINCT(SPLIT(words, ";"))
distinct_word_count:long |
---|
5 |
Counts are approximate
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 threshold. The precision_threshold options allows to trade memory
for accuracy, and defines a unique count below which counts are expected to be
close to accurate. Above this value, counts might become a bit more fuzzy. The
maximum supported value is 40000, thresholds above this number will have the
same effect as a threshold of 40000. The default value is 3000
.
MAX
editSyntax
Parameters
-
field
Description
The maximum value of a field.
Supported types
field | result |
---|---|
boolean |
boolean |
date |
date |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
version |
version |
Examples
FROM employees | STATS MAX(languages)
MAX(languages):integer |
---|
5 |
The expression can use inline functions. For example, to calculate the maximum over an average of a multivalued column, use MV_AVG
to first average the multiple values per row, and use the result with the MAX
function
FROM employees | STATS max_avg_salary_change = MAX(MV_AVG(salary_change))
max_avg_salary_change:double |
---|
13.75 |
MEDIAN
editSyntax
Parameters
-
number
Description
The value that is greater than half of all values and less than half of all values, also known as the 50% PERCENTILE
.
Like PERCENTILE
, MEDIAN
is usually approximate.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
Examples
FROM employees | STATS MEDIAN(salary), PERCENTILE(salary, 50)
MEDIAN(salary):double | PERCENTILE(salary, 50):double |
---|---|
47003 |
47003 |
The expression can use inline functions. For example, to calculate the median of the maximum values of a multivalued column, first use MV_MAX
to get the maximum value per row, and use the result with the MEDIAN
function
FROM employees | STATS median_max_salary_change = MEDIAN(MV_MAX(salary_change))
median_max_salary_change:double |
---|
7.69 |
MEDIAN
is also non-deterministic.
This means you can get slightly different results using the same data.
MEDIAN_ABSOLUTE_DEVIATION
editSyntax
Parameters
-
number
Description
Returns the median absolute deviation, a measure of variability. It is a robust statistic, meaning that it is useful for describing data that may have outliers, or may not be normally distributed. For such data it can be more descriptive than standard deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X
, the median absolute deviation is median(|median(X) - X|)
.
Like PERCENTILE
, MEDIAN_ABSOLUTE_DEVIATION
is usually approximate.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
Examples
FROM employees | STATS MEDIAN(salary), MEDIAN_ABSOLUTE_DEVIATION(salary)
MEDIAN(salary):double | MEDIAN_ABSOLUTE_DEVIATION(salary):double |
---|---|
47003 |
10096.5 |
The expression can use inline functions. For example, to calculate the median absolute deviation of the maximum values of a multivalued column, first use MV_MAX
to get the maximum value per row, and use the result with the MEDIAN_ABSOLUTE_DEVIATION
function
FROM employees | STATS m_a_d_max_salary_change = MEDIAN_ABSOLUTE_DEVIATION(MV_MAX(salary_change))
m_a_d_max_salary_change:double |
---|
5.69 |
MEDIAN_ABSOLUTE_DEVIATION
is also non-deterministic.
This means you can get slightly different results using the same data.
MIN
editSyntax
Parameters
-
field
Description
The minimum value of a field.
Supported types
field | result |
---|---|
boolean |
boolean |
date |
date |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
version |
version |
Examples
FROM employees | STATS MIN(languages)
MIN(languages):integer |
---|
1 |
The expression can use inline functions. For example, to calculate the minimum over an average of a multivalued column, use MV_AVG
to first average the multiple values per row, and use the result with the MIN
function
FROM employees | STATS min_avg_salary_change = MIN(MV_AVG(salary_change))
min_avg_salary_change:double |
---|
-8.46 |
PERCENTILE
editSyntax
Parameters
-
number
-
percentile
Description
Returns the value at which a certain percentage of observed values occur. For example, the 95th percentile is the value which is greater than 95% of the observed values and the 50th percentile is the MEDIAN
.
Supported types
number | percentile | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
long |
double |
double |
long |
integer |
double |
long |
long |
double |
Examples
FROM employees | STATS p0 = PERCENTILE(salary, 0) , p50 = PERCENTILE(salary, 50) , p99 = PERCENTILE(salary, 99)
p0:double | p50:double | p99:double |
---|---|---|
25324 |
47003 |
74970.29 |
The expression can use inline functions. For example, to calculate a percentile of the maximum values of a multivalued column, first use MV_MAX
to get the maximum value per row, and use the result with the PERCENTILE
function
FROM employees | STATS p80_max_salary_change = PERCENTILE(MV_MAX(salary_change), 80)
p80_max_salary_change:double |
---|
12.132 |
PERCENTILE
is (usually) approximate
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.
ST_CENTROID_AGG
editSyntax
Parameters
-
field
Description
Calculate the spatial centroid over a field with spatial point geometry type.
Supported types
field | result |
---|---|
cartesian_point |
cartesian_point |
geo_point |
geo_point |
Example
FROM airports | STATS centroid=ST_CENTROID_AGG(location)
centroid:geo_point |
---|
POINT(-0.030548143003023033 24.37553649504829) |
SUM
editSyntax
Parameters
-
number
Description
The sum of a numeric expression.
Supported types
number | result |
---|---|
double |
double |
integer |
long |
long |
long |
Examples
FROM employees | STATS SUM(languages)
SUM(languages):long |
---|
281 |
The expression can use inline functions. For example, to calculate the sum of each employee’s maximum salary changes, apply the MV_MAX
function to each row and then sum the results
FROM employees | STATS total_salary_changes = SUM(MV_MAX(salary_change))
total_salary_changes:double |
---|
446.75 |
TOP
editSyntax
Parameters
-
field
- The field to collect the top values for.
-
limit
- The maximum number of values to collect.
-
order
-
The order to calculate the top values. Either
asc
ordesc
.
Description
Collects the top values for a field. Includes repeated values.
Supported types
field | limit | order | result |
---|---|---|---|
boolean |
integer |
keyword |
boolean |
date |
integer |
keyword |
date |
double |
integer |
keyword |
double |
integer |
integer |
keyword |
integer |
ip |
integer |
keyword |
ip |
keyword |
integer |
keyword |
keyword |
long |
integer |
keyword |
long |
text |
integer |
keyword |
keyword |
Example
FROM employees | STATS top_salaries = TOP(salary, 3, "desc"), top_salary = MAX(salary)
top_salaries:integer | top_salary:integer |
---|---|
[74999, 74970, 74572] |
74999 |
VALUES
editDo not use on production environments. This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Syntax
Parameters
-
field
Description
Returns all values in a group as a multivalued field. The order of the returned values isn’t guaranteed. If you need the values returned in order use MV_SORT
.
Supported types
field | result |
---|---|
boolean |
boolean |
date |
date |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
version |
version |
Example
FROM employees | EVAL first_letter = SUBSTRING(first_name, 0, 1) | STATS first_name=MV_SORT(VALUES(first_name)) BY first_letter | SORT first_letter
first_name:keyword | first_letter:keyword |
---|---|
[Alejandro, Amabile, Anneke, Anoosh, Arumugam] |
A |
[Basil, Berhard, Berni, Bezalel, Bojan, Breannda, Brendon] |
B |
[Charlene, Chirstian, Claudi, Cristinel] |
C |
[Danel, Divier, Domenick, Duangkaew] |
D |
[Ebbe, Eberhardt, Erez] |
E |
Florian |
F |
[Gao, Georgi, Georgy, Gino, Guoxiang] |
G |
[Heping, Hidefumi, Hilari, Hironobu, Hironoby, Hisao] |
H |
[Jayson, Jungsoon] |
J |
[Kazuhide, Kazuhito, Kendra, Kenroku, Kshitij, Kwee, Kyoichi] |
K |
[Lillian, Lucien] |
L |
[Magy, Margareta, Mary, Mayuko, Mayumi, Mingsen, Mokhtar, Mona, Moss] |
M |
Otmar |
O |
[Parto, Parviz, Patricio, Prasadram, Premal] |
P |
[Ramzi, Remzi, Reuven] |
R |
[Sailaja, Saniya, Sanjiv, Satosi, Shahaf, Shir, Somnath, Sreekrishna, Sudharsan, Sumant, Suzette] |
S |
[Tse, Tuval, Tzvetan] |
T |
[Udi, Uri] |
U |
[Valdiodio, Valter, Vishv] |
V |
Weiyi |
W |
Xinglin |
X |
[Yinghua, Yishay, Yongqiao] |
Y |
[Zhongwei, Zvonko] |
Z |
null |
null |
This can use a significant amount of memory and ES|QL doesn’t yet grow aggregations beyond memory. So this aggregation will work until it is used to collect more values than can fit into memory. Once it collects too many values it will fail the query with a Circuit Breaker Error.
WEIGHTED_AVG
editSyntax
Parameters
-
number
- A numeric value.
-
weight
- A numeric weight.
Description
The weighted average of a numeric expression.
Supported types
number | weight | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
long |
double |
double |
long |
integer |
double |
long |
long |
double |
Example
FROM employees | STATS w_avg = WEIGHTED_AVG(salary, height) by languages | EVAL w_avg = ROUND(w_avg) | KEEP w_avg, languages | SORT languages
w_avg:double | languages:integer |
---|---|
51464.0 |
1 |
48477.0 |
2 |
52379.0 |
3 |
47990.0 |
4 |
42119.0 |
5 |
52142.0 |
null |
ES|QL grouping functions
editThe STATS ... BY
command supports these grouping functions:
BUCKET
editSyntax
Parameters
-
field
- Numeric or date expression from which to derive buckets.
-
buckets
-
Target number of buckets, or desired bucket size if
from
andto
parameters are omitted. -
from
- Start of the range. Can be a number, a date or a date expressed as a string.
-
to
- End of the range. Can be a number, a date or a date expressed as a string.
Description
Creates groups of values - buckets - out of a datetime or numeric input. The size of the buckets can either be provided directly, or chosen based on a recommended count and values range.
Supported types
field | buckets | from | to | result |
---|---|---|---|---|
date |
date_period |
date |
||
date |
integer |
date |
date |
date |
date |
integer |
date |
keyword |
date |
date |
integer |
date |
text |
date |
date |
integer |
keyword |
date |
date |
date |
integer |
keyword |
keyword |
date |
date |
integer |
keyword |
text |
date |
date |
integer |
text |
date |
date |
date |
integer |
text |
keyword |
date |
date |
integer |
text |
text |
date |
date |
time_duration |
date |
||
double |
double |
double |
||
double |
integer |
double |
double |
double |
double |
integer |
double |
integer |
double |
double |
integer |
double |
long |
double |
double |
integer |
integer |
double |
double |
double |
integer |
integer |
integer |
double |
double |
integer |
integer |
long |
double |
double |
integer |
long |
double |
double |
double |
integer |
long |
integer |
double |
double |
integer |
long |
long |
double |
double |
integer |
double |
||
double |
long |
double |
||
integer |
double |
double |
||
integer |
integer |
double |
double |
double |
integer |
integer |
double |
integer |
double |
integer |
integer |
double |
long |
double |
integer |
integer |
integer |
double |
double |
integer |
integer |
integer |
integer |
double |
integer |
integer |
integer |
long |
double |
integer |
integer |
long |
double |
double |
integer |
integer |
long |
integer |
double |
integer |
integer |
long |
long |
double |
integer |
integer |
double |
||
integer |
long |
double |
||
long |
double |
double |
||
long |
integer |
double |
double |
double |
long |
integer |
double |
integer |
double |
long |
integer |
double |
long |
double |
long |
integer |
integer |
double |
double |
long |
integer |
integer |
integer |
double |
long |
integer |
integer |
long |
double |
long |
integer |
long |
double |
double |
long |
integer |
long |
integer |
double |
long |
integer |
long |
long |
double |
long |
integer |
double |
||
long |
long |
double |
Examples
BUCKET
can work in two modes: one in which the size of the bucket is computed
based on a buckets count recommendation (four parameters) and a range, and
another in which the bucket size is provided directly (two parameters).
Using a target number of buckets, a start of a range, and an end of a range,
BUCKET
picks an appropriate bucket size to generate the target number of buckets or fewer.
For example, asking for at most 20 buckets over a year results in monthly buckets:
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS hire_date = MV_SORT(VALUES(hire_date)) BY month = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z") | SORT hire_date
hire_date:date | month:date |
---|---|
[1985-02-18T00:00:00.000Z, 1985-02-24T00:00:00.000Z] |
1985-02-01T00:00:00.000Z |
1985-05-13T00:00:00.000Z |
1985-05-01T00:00:00.000Z |
1985-07-09T00:00:00.000Z |
1985-07-01T00:00:00.000Z |
1985-09-17T00:00:00.000Z |
1985-09-01T00:00:00.000Z |
[1985-10-14T00:00:00.000Z, 1985-10-20T00:00:00.000Z] |
1985-10-01T00:00:00.000Z |
[1985-11-19T00:00:00.000Z, 1985-11-20T00:00:00.000Z, 1985-11-21T00:00:00.000Z] |
1985-11-01T00:00:00.000Z |
The goal isn’t to provide exactly the target number of buckets, it’s to pick a range that people are comfortable with that provides at most the target number of buckets.
Combine BUCKET
with an aggregation to create a histogram:
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS hires_per_month = COUNT(*) BY month = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z") | SORT month
hires_per_month:long | month:date |
---|---|
2 |
1985-02-01T00:00:00.000Z |
1 |
1985-05-01T00:00:00.000Z |
1 |
1985-07-01T00:00:00.000Z |
1 |
1985-09-01T00:00:00.000Z |
2 |
1985-10-01T00:00:00.000Z |
4 |
1985-11-01T00:00:00.000Z |
BUCKET
does not create buckets that don’t match any documents.
That’s why this example is missing 1985-03-01
and other dates.
Asking for more buckets can result in a smaller range. For example, asking for at most 100 buckets in a year results in weekly buckets:
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, 100, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z") | SORT week
hires_per_week:long | week:date |
---|---|
2 |
1985-02-18T00:00:00.000Z |
1 |
1985-05-13T00:00:00.000Z |
1 |
1985-07-08T00:00:00.000Z |
1 |
1985-09-16T00:00:00.000Z |
2 |
1985-10-14T00:00:00.000Z |
4 |
1985-11-18T00:00:00.000Z |
BUCKET
does not filter any rows. It only uses the provided range to pick a good bucket size.
For rows with a value outside of the range, it returns a bucket value that corresponds to a bucket outside the range.
Combine`BUCKET` with WHERE
to filter rows.
If the desired bucket size is known in advance, simply provide it as the second argument, leaving the range out:
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, 1 week) | SORT week
hires_per_week:long | week:date |
---|---|
2 |
1985-02-18T00:00:00.000Z |
1 |
1985-05-13T00:00:00.000Z |
1 |
1985-07-08T00:00:00.000Z |
1 |
1985-09-16T00:00:00.000Z |
2 |
1985-10-14T00:00:00.000Z |
4 |
1985-11-18T00:00:00.000Z |
When providing the bucket size as the second parameter, it must be a time duration or date period.
BUCKET
can also operate on numeric fields. For example, to create a salary histogram:
FROM employees | STATS COUNT(*) by bs = BUCKET(salary, 20, 25324, 74999) | SORT bs
COUNT(*):long | bs:double |
---|---|
9 |
25000.0 |
9 |
30000.0 |
18 |
35000.0 |
11 |
40000.0 |
11 |
45000.0 |
10 |
50000.0 |
7 |
55000.0 |
9 |
60000.0 |
8 |
65000.0 |
8 |
70000.0 |
Unlike the earlier example that intentionally filters on a date range, you rarely want to filter on a numeric range.
You have to find the min
and max
separately. ES|QL doesn’t yet have an easy way to do that automatically.
The range can be omitted if the desired bucket size is known in advance. Simply provide it as the second argument:
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS c = COUNT(1) BY b = BUCKET(salary, 5000.) | SORT b
c:long | b:double |
---|---|
1 |
25000.0 |
1 |
30000.0 |
1 |
40000.0 |
2 |
45000.0 |
2 |
50000.0 |
1 |
55000.0 |
1 |
60000.0 |
1 |
65000.0 |
1 |
70000.0 |
Create hourly buckets for the last 24 hours, and calculate the number of events per hour:
FROM sample_data | WHERE @timestamp >= NOW() - 1 day and @timestamp < NOW() | STATS COUNT(*) BY bucket = BUCKET(@timestamp, 25, NOW() - 1 day, NOW())
COUNT(*):long | bucket:date |
---|
Create monthly buckets for the year 1985, and calculate the average salary by hiring month
FROM employees | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z" | STATS AVG(salary) BY bucket = BUCKET(hire_date, 20, "1985-01-01T00:00:00Z", "1986-01-01T00:00:00Z") | SORT bucket
AVG(salary):double | bucket:date |
---|---|
46305.0 |
1985-02-01T00:00:00.000Z |
44817.0 |
1985-05-01T00:00:00.000Z |
62405.0 |
1985-07-01T00:00:00.000Z |
49095.0 |
1985-09-01T00:00:00.000Z |
51532.0 |
1985-10-01T00:00:00.000Z |
54539.75 |
1985-11-01T00:00:00.000Z |
BUCKET
may be used in both the aggregating and grouping part of the
STATS … BY … command provided that in the aggregating
part the function is referenced by an alias defined in the
grouping part, or that it is invoked with the exact same expression:
FROM employees | STATS s1 = b1 + 1, s2 = BUCKET(salary / 1000 + 999, 50.) + 2 BY b1 = BUCKET(salary / 100 + 99, 50.), b2 = BUCKET(salary / 1000 + 999, 50.) | SORT b1, b2 | KEEP s1, b1, s2, b2
s1:double | b1:double | s2:double | b2:double |
---|---|---|---|
351.0 |
350.0 |
1002.0 |
1000.0 |
401.0 |
400.0 |
1002.0 |
1000.0 |
451.0 |
450.0 |
1002.0 |
1000.0 |
501.0 |
500.0 |
1002.0 |
1000.0 |
551.0 |
550.0 |
1002.0 |
1000.0 |
601.0 |
600.0 |
1002.0 |
1000.0 |
601.0 |
600.0 |
1052.0 |
1050.0 |
651.0 |
650.0 |
1052.0 |
1050.0 |
701.0 |
700.0 |
1052.0 |
1050.0 |
751.0 |
750.0 |
1052.0 |
1050.0 |
801.0 |
800.0 |
1052.0 |
1050.0 |
ES|QL conditional functions and expressions
editConditional functions return one of their arguments by evaluating in an if-else manner. ES|QL supports these conditional functions:
CASE
editSyntax
Parameters
-
condition
- A condition.
-
trueValue
-
The value that’s returned when the corresponding condition is the first to evaluate to
true
. The default value is returned when no condition matches. -
elseValue
-
The value that’s returned when no condition evaluates to
true
.
Description
Accepts pairs of conditions and values. The function returns the value that belongs to the first condition that evaluates to true
. If the number of arguments is odd, the last argument is the default value which is returned when no condition matches. If the number of arguments is even, and no condition matches, the function returns null
.
Supported types
condition | trueValue | elseValue | result |
---|---|---|---|
boolean |
boolean |
boolean |
boolean |
boolean |
boolean |
boolean |
|
boolean |
cartesian_point |
cartesian_point |
cartesian_point |
boolean |
cartesian_point |
cartesian_point |
|
boolean |
cartesian_shape |
cartesian_shape |
cartesian_shape |
boolean |
cartesian_shape |
cartesian_shape |
|
boolean |
date |
date |
date |
boolean |
date |
date |
|
boolean |
double |
double |
double |
boolean |
double |
double |
|
boolean |
geo_point |
geo_point |
geo_point |
boolean |
geo_point |
geo_point |
|
boolean |
geo_shape |
geo_shape |
geo_shape |
boolean |
geo_shape |
geo_shape |
|
boolean |
integer |
integer |
integer |
boolean |
integer |
integer |
|
boolean |
ip |
ip |
ip |
boolean |
ip |
ip |
|
boolean |
keyword |
keyword |
keyword |
boolean |
keyword |
text |
keyword |
boolean |
keyword |
keyword |
|
boolean |
long |
long |
long |
boolean |
long |
long |
|
boolean |
text |
keyword |
keyword |
boolean |
text |
text |
keyword |
boolean |
text |
keyword |
|
boolean |
unsigned_long |
unsigned_long |
unsigned_long |
boolean |
unsigned_long |
unsigned_long |
|
boolean |
version |
version |
version |
boolean |
version |
version |
Examples
Determine whether employees are monolingual, bilingual, or polyglot:
FROM employees | EVAL type = CASE( languages <= 1, "monolingual", languages <= 2, "bilingual", "polyglot") | KEEP emp_no, languages, type
emp_no:integer | languages:integer | type:keyword |
---|---|---|
10001 |
2 |
bilingual |
10002 |
5 |
polyglot |
10003 |
4 |
polyglot |
10004 |
5 |
polyglot |
10005 |
1 |
monolingual |
Calculate the total connection success rate based on log messages:
FROM sample_data | EVAL successful = CASE( STARTS_WITH(message, "Connected to"), 1, message == "Connection error", 0 ) | STATS success_rate = AVG(successful)
success_rate:double |
---|
0.5 |
Calculate an hourly error rate as a percentage of the total number of log messages:
FROM sample_data | EVAL error = CASE(message LIKE "*error*", 1, 0) | EVAL hour = DATE_TRUNC(1 hour, @timestamp) | STATS error_rate = AVG(error) by hour | SORT hour
error_rate:double | hour:date |
---|---|
0.0 |
2023-10-23T12:00:00.000Z |
0.6 |
2023-10-23T13:00:00.000Z |
COALESCE
editSyntax
Parameters
-
first
- Expression to evaluate.
-
rest
- Other expression to evaluate.
Description
Returns the first of its arguments that is not null. If all arguments are null, it returns null
.
Supported types
first | rest | result |
---|---|---|
boolean |
boolean |
boolean |
boolean |
boolean |
|
cartesian_point |
cartesian_point |
cartesian_point |
cartesian_shape |
cartesian_shape |
cartesian_shape |
date |
date |
date |
geo_point |
geo_point |
geo_point |
geo_shape |
geo_shape |
geo_shape |
integer |
integer |
integer |
integer |
integer |
|
ip |
ip |
ip |
keyword |
keyword |
keyword |
keyword |
keyword |
|
long |
long |
long |
long |
long |
|
text |
text |
keyword |
text |
keyword |
|
version |
version |
version |
Example
ROW a=null, b="b" | EVAL COALESCE(a, b)
a:null | b:keyword | COALESCE(a, b):keyword |
---|---|---|
null |
b |
b |
GREATEST
editSyntax
Parameters
-
first
- First of the columns to evaluate.
-
rest
- The rest of the columns to evaluate.
Description
Returns the maximum value from multiple columns. This is similar to MV_MAX
except it is intended to run on multiple columns at once.
When run on keyword
or text
fields, this returns the last string in alphabetical order. When run on boolean
columns this will return true
if any values are true
.
Supported types
first | rest | result |
---|---|---|
boolean |
boolean |
boolean |
boolean |
boolean |
|
date |
date |
date |
double |
double |
double |
integer |
integer |
integer |
integer |
integer |
|
ip |
ip |
ip |
keyword |
keyword |
keyword |
keyword |
keyword |
|
long |
long |
long |
long |
long |
|
text |
text |
keyword |
text |
keyword |
|
version |
version |
version |
Example
ROW a = 10, b = 20 | EVAL g = GREATEST(a, b)
a:integer | b:integer | g:integer |
---|---|---|
10 |
20 |
20 |
LEAST
editSyntax
Parameters
-
first
- First of the columns to evaluate.
-
rest
- The rest of the columns to evaluate.
Description
Returns the minimum value from multiple columns. This is similar to MV_MIN
except it is intended to run on multiple columns at once.
Supported types
first | rest | result |
---|---|---|
boolean |
boolean |
boolean |
boolean |
boolean |
|
date |
date |
date |
double |
double |
double |
integer |
integer |
integer |
integer |
integer |
|
ip |
ip |
ip |
keyword |
keyword |
keyword |
keyword |
keyword |
|
long |
long |
long |
long |
long |
|
text |
text |
keyword |
text |
keyword |
|
version |
version |
version |
Example
ROW a = 10, b = 20 | EVAL l = LEAST(a, b)
a:integer | b:integer | l:integer |
---|---|---|
10 |
20 |
10 |
ES|QL date-time functions
editES|QL supports these date-time functions:
DATE_DIFF
editSyntax
Parameters
-
unit
- Time difference unit
-
startTimestamp
- A string representing a start timestamp
-
endTimestamp
- A string representing an end timestamp
Description
Subtracts the startTimestamp
from the endTimestamp
and returns the difference in multiples of unit
. If startTimestamp
is later than the endTimestamp
, negative values are returned.
Datetime difference units | |
---|---|
unit |
abbreviations |
year |
years, yy, yyyy |
quarter |
quarters, qq, q |
month |
months, mm, m |
dayofyear |
dy, y |
day |
days, dd, d |
week |
weeks, wk, ww |
weekday |
weekdays, dw |
hour |
hours, hh |
minute |
minutes, mi, n |
second |
seconds, ss, s |
millisecond |
milliseconds, ms |
microsecond |
microseconds, mcs |
nanosecond |
nanoseconds, ns |
Note that while there is an overlap between the function’s supported units and ES|QL’s supported time span literals, these sets are distinct and not interchangeable. Similarly, the supported abbreviations are conveniently shared with implementations of this function in other established products and not necessarily common with the date-time nomenclature used by Elasticsearch.
Supported types
unit | startTimestamp | endTimestamp | result |
---|---|---|---|
keyword |
date |
date |
integer |
text |
date |
date |
integer |
Examples
ROW date1 = TO_DATETIME("2023-12-02T11:00:00.000Z"), date2 = TO_DATETIME("2023-12-02T11:00:00.001Z") | EVAL dd_ms = DATE_DIFF("microseconds", date1, date2)
date1:date | date2:date | dd_ms:integer |
---|---|---|
2023-12-02T11:00:00.000Z |
2023-12-02T11:00:00.001Z |
1000 |
When subtracting in calendar units - like year, month a.s.o. - only the fully elapsed units are counted. To avoid this and obtain also remainders, simply switch to the next smaller unit and do the date math accordingly.
ROW end_23=TO_DATETIME("2023-12-31T23:59:59.999Z"), start_24=TO_DATETIME("2024-01-01T00:00:00.000Z"), end_24=TO_DATETIME("2024-12-31T23:59:59.999") | EVAL end23_to_start24=DATE_DIFF("year", end_23, start_24) | EVAL end23_to_end24=DATE_DIFF("year", end_23, end_24) | EVAL start_to_end_24=DATE_DIFF("year", start_24, end_24)
end_23:date | start_24:date | end_24:date | end23_to_start24:integer | end23_to_end24:integer | start_to_end_24:integer |
---|---|---|---|---|---|
2023-12-31T23:59:59.999Z |
2024-01-01T00:00:00.000Z |
2024-12-31T23:59:59.999Z |
0 |
1 |
0 |
DATE_EXTRACT
editSyntax
Parameters
-
datePart
-
Part of the date to extract. Can be:
aligned_day_of_week_in_month
,aligned_day_of_week_in_year
,aligned_week_of_month
,aligned_week_of_year
,ampm_of_day
,clock_hour_of_ampm
,clock_hour_of_day
,day_of_month
,day_of_week
,day_of_year
,epoch_day
,era
,hour_of_ampm
,hour_of_day
,instant_seconds
,micro_of_day
,micro_of_second
,milli_of_day
,milli_of_second
,minute_of_day
,minute_of_hour
,month_of_year
,nano_of_day
,nano_of_second
,offset_seconds
,proleptic_month
,second_of_day
,second_of_minute
,year
, oryear_of_era
. Refer to java.time.temporal.ChronoField for a description of these values. Ifnull
, the function returnsnull
. -
date
-
Date expression. If
null
, the function returnsnull
.
Description
Extracts parts of a date, like year, month, day, hour.
Supported types
datePart | date | result |
---|---|---|
keyword |
date |
long |
text |
date |
long |
Examples
ROW date = DATE_PARSE("yyyy-MM-dd", "2022-05-06") | EVAL year = DATE_EXTRACT("year", date)
date:date | year:long |
---|---|
2022-05-06T00:00:00.000Z |
2022 |
Find all events that occurred outside of business hours (before 9 AM or after 5PM), on any given date:
FROM sample_data | WHERE DATE_EXTRACT("hour_of_day", @timestamp) < 9 AND DATE_EXTRACT("hour_of_day", @timestamp) >= 17
@timestamp:date | client_ip:ip | event_duration:long | message:keyword |
---|
DATE_FORMAT
editSyntax
Parameters
-
dateFormat
-
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.
Supported types
dateFormat | date | result |
---|---|---|
keyword |
date |
keyword |
text |
date |
keyword |
Example
FROM employees | KEEP first_name, last_name, hire_date | EVAL hired = DATE_FORMAT("yyyy-MM-dd", hire_date)
first_name:keyword | last_name:keyword | hire_date:date | hired:keyword |
---|---|---|---|
Alejandro |
McAlpine |
1991-06-26T00:00:00.000Z |
1991-06-26 |
Amabile |
Gomatam |
1992-11-18T00:00:00.000Z |
1992-11-18 |
Anneke |
Preusig |
1989-06-02T00:00:00.000Z |
1989-06-02 |
DATE_PARSE
editSyntax
Parameters
-
datePattern
-
The date format. Refer to the
DateTimeFormatter
documentation for the syntax. Ifnull
, the function returnsnull
. -
dateString
-
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.
Supported types
datePattern | dateString | result |
---|---|---|
keyword |
keyword |
date |
keyword |
text |
date |
text |
keyword |
date |
text |
text |
date |
Example
ROW date_string = "2022-05-06" | EVAL date = DATE_PARSE("yyyy-MM-dd", date_string)
date_string:keyword | date:date |
---|---|
2022-05-06 |
2022-05-06T00:00:00.000Z |
DATE_TRUNC
editSyntax
Parameters
-
interval
- Interval; expressed using the timespan literal syntax.
-
date
- Date expression
Description
Rounds down a date to the closest interval.
Supported types
interval | date | result |
---|---|---|
date_period |
date |
date |
time_duration |
date |
date |
Examples
FROM employees | KEEP first_name, last_name, hire_date | EVAL year_hired = DATE_TRUNC(1 year, hire_date)
first_name:keyword | last_name:keyword | hire_date:date | year_hired:date |
---|---|---|---|
Alejandro |
McAlpine |
1991-06-26T00:00:00.000Z |
1991-01-01T00:00:00.000Z |
Amabile |
Gomatam |
1992-11-18T00:00:00.000Z |
1992-01-01T00:00:00.000Z |
Anneke |
Preusig |
1989-06-02T00:00:00.000Z |
1989-01-01T00:00:00.000Z |
Combine DATE_TRUNC
with STATS ... BY
to create date histograms. For
example, the number of hires per year:
FROM employees | EVAL year = DATE_TRUNC(1 year, hire_date) | STATS hires = COUNT(emp_no) BY year | SORT year
hires:long | year:date |
---|---|
11 |
1985-01-01T00:00:00.000Z |
11 |
1986-01-01T00:00:00.000Z |
15 |
1987-01-01T00:00:00.000Z |
9 |
1988-01-01T00:00:00.000Z |
13 |
1989-01-01T00:00:00.000Z |
12 |
1990-01-01T00:00:00.000Z |
6 |
1991-01-01T00:00:00.000Z |
8 |
1992-01-01T00:00:00.000Z |
3 |
1993-01-01T00:00:00.000Z |
4 |
1994-01-01T00:00:00.000Z |
5 |
1995-01-01T00:00:00.000Z |
1 |
1996-01-01T00:00:00.000Z |
1 |
1997-01-01T00:00:00.000Z |
1 |
1999-01-01T00:00:00.000Z |
Or an hourly error rate:
FROM sample_data | EVAL error = CASE(message LIKE "*error*", 1, 0) | EVAL hour = DATE_TRUNC(1 hour, @timestamp) | STATS error_rate = AVG(error) by hour | SORT hour
error_rate:double | hour:date |
---|---|
0.0 |
2023-10-23T12:00:00.000Z |
0.6 |
2023-10-23T13:00:00.000Z |
NOW
editSyntax
Parameters
Description
Returns current date and time.
Supported types
result |
---|
date |
Examples
ROW current_date = NOW()
y:keyword |
---|
20 |
To retrieve logs from the last hour:
FROM sample_data | WHERE @timestamp > NOW() - 1 hour
@timestamp:date | client_ip:ip | event_duration:long | message:keyword |
---|
ES|QL IP functions
editES|QL supports these IP functions:
CIDR_MATCH
editSyntax
Parameters
-
ip
-
IP address of type
ip
(both IPv4 and IPv6 are supported). -
blockX
- CIDR block to test the IP against.
Description
Returns true if the provided IP is contained in one of the provided CIDR blocks.
Supported types
ip | blockX | result |
---|---|---|
ip |
keyword |
boolean |
ip |
text |
boolean |
Example
FROM hosts | WHERE CIDR_MATCH(ip1, "127.0.0.2/32", "127.0.0.3/32") | KEEP card, host, ip0, ip1
card:keyword | host:keyword | ip0:ip | ip1:ip |
---|---|---|---|
eth1 |
beta |
127.0.0.1 |
127.0.0.2 |
eth0 |
gamma |
fe80::cae2:65ff:fece:feb9 |
127.0.0.3 |
IP_PREFIX
editSyntax
Parameters
-
ip
-
IP address of type
ip
(both IPv4 and IPv6 are supported). -
prefixLengthV4
- Prefix length for IPv4 addresses.
-
prefixLengthV6
- Prefix length for IPv6 addresses.
Description
Truncates an IP to a given prefix length.
Supported types
ip | prefixLengthV4 | prefixLengthV6 | result |
---|---|---|---|
ip |
integer |
integer |
ip |
Example
row ip4 = to_ip("1.2.3.4"), ip6 = to_ip("fe80::cae2:65ff:fece:feb9") | eval ip4_prefix = ip_prefix(ip4, 24, 0), ip6_prefix = ip_prefix(ip6, 0, 112);
ip4:ip | ip6:ip | ip4_prefix:ip | ip6_prefix:ip |
---|---|---|---|
1.2.3.4 |
fe80::cae2:65ff:fece:feb9 |
1.2.3.0 |
fe80::cae2:65ff:fece:0000 |
ES|QL mathematical functions
editES|QL supports these mathematical functions:
ABS
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the absolute value.
Supported types
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Examples
ROW number = -1.0 | EVAL abs_number = ABS(number)
number:double | abs_number:double |
---|---|
-1.0 |
1.0 |
FROM employees | KEEP first_name, last_name, height | EVAL abs_height = ABS(0.0 - height)
first_name:keyword | last_name:keyword | height:double | abs_height:double |
---|---|---|---|
Alejandro |
McAlpine |
1.48 |
1.48 |
Amabile |
Gomatam |
2.09 |
2.09 |
Anneke |
Preusig |
1.56 |
1.56 |
ACOS
editSyntax
Parameters
-
number
-
Number between -1 and 1. If
null
, the function returnsnull
.
Description
Returns the arccosine of n
as an angle, expressed in radians.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=.9 | EVAL acos=ACOS(a)
a:double | acos:double |
---|---|
.9 |
0.45102681179626236 |
ASIN
editSyntax
Parameters
-
number
-
Number between -1 and 1. If
null
, the function returnsnull
.
Description
Returns the arcsine of the input numeric expression as an angle, expressed in radians.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=.9 | EVAL asin=ASIN(a)
a:double | asin:double |
---|---|
.9 |
1.1197695149986342 |
ATAN
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the arctangent of the input numeric expression as an angle, expressed in radians.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=12.9 | EVAL atan=ATAN(a)
a:double | atan:double |
---|---|
12.9 |
1.4934316673669235 |
ATAN2
editSyntax
Parameters
-
y_coordinate
-
y coordinate. If
null
, the function returnsnull
. -
x_coordinate
-
x coordinate. 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_coordinate | x_coordinate | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
double |
unsigned_long |
double |
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
integer |
unsigned_long |
double |
long |
double |
double |
long |
integer |
double |
long |
long |
double |
long |
unsigned_long |
double |
unsigned_long |
double |
double |
unsigned_long |
integer |
double |
unsigned_long |
long |
double |
unsigned_long |
unsigned_long |
double |
Example
ROW y=12.9, x=.6 | EVAL atan2=ATAN2(y, x)
y:double | x:double | atan2:double |
---|---|---|
12.9 |
0.6 |
1.5243181954438936 |
CBRT
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the cube root of a number. The input can be any numeric value, the return value is always a double. Cube roots of infinities are null.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW d = 1000.0 | EVAL c = cbrt(d)
d: double | c:double |
---|---|
1000.0 |
10.0 |
CEIL
editSyntax
Parameters
-
number
-
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
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Example
ROW a=1.8 | EVAL a=CEIL(a)
a:double |
---|
2 |
COS
editSyntax
Parameters
-
angle
-
An angle, in radians. If
null
, the function returnsnull
.
Description
Returns the cosine of an angle.
Supported types
angle | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL cos=COS(a)
a:double | cos:double |
---|---|
1.8 |
-0.2272020946930871 |
COSH
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the hyperbolic cosine of a number.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL cosh=COSH(a)
a:double | cosh:double |
---|---|
1.8 |
3.1074731763172667 |
E
editSyntax
Parameters
Description
Returns Euler’s number.
Supported types
result |
---|
double |
Example
ROW E()
E():double |
---|
2.718281828459045 |
EXP
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the value of e raised to the power of the given number.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW d = 5.0 | EVAL s = EXP(d)
d: double | s:double |
---|---|
5.0 |
148.413159102576603 |
FLOOR
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Round a number down to the nearest integer.
This is a noop for long
(including unsigned) and integer
.
For double
this picks the closest double
value to the integer
similar to Math.floor.
Supported types
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Example
ROW a=1.8 | EVAL a=FLOOR(a)
a:double |
---|
1 |
HYPOT
editSyntax
Parameters
-
number1
-
Numeric expression. If
null
, the function returnsnull
. -
number2
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the hypotenuse of two numbers. The input can be any numeric values, the return value is always a double. Hypotenuses of infinities are null.
Supported types
number1 | number2 | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
double |
unsigned_long |
double |
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
integer |
unsigned_long |
double |
long |
double |
double |
long |
integer |
double |
long |
long |
double |
long |
unsigned_long |
double |
unsigned_long |
double |
double |
unsigned_long |
integer |
double |
unsigned_long |
long |
double |
unsigned_long |
unsigned_long |
double |
Example
ROW a = 3.0, b = 4.0 | EVAL c = HYPOT(a, b)
a:double | b:double | c:double |
---|---|---|
3.0 |
4.0 |
5.0 |
LOG
editSyntax
Parameters
-
base
-
Base of logarithm. If
null
, the function returnsnull
. If not provided, this function returns the natural logarithm (base e) of a value. -
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the logarithm of a value to a base. The input can be any numeric value, the return value is always a double. Logs of zero, negative numbers, and base of one return null
as well as a warning.
Supported types
base | number | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
double |
unsigned_long |
double |
double |
double |
|
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
integer |
unsigned_long |
double |
integer |
double |
|
long |
double |
double |
long |
integer |
double |
long |
long |
double |
long |
unsigned_long |
double |
long |
double |
|
unsigned_long |
double |
double |
unsigned_long |
integer |
double |
unsigned_long |
long |
double |
unsigned_long |
unsigned_long |
double |
unsigned_long |
double |
Examples
ROW base = 2.0, value = 8.0 | EVAL s = LOG(base, value)
base: double | value: double | s:double |
---|---|---|
2.0 |
8.0 |
3.0 |
row value = 100 | EVAL s = LOG(value);
value: integer | s:double |
---|---|
100 |
4.605170185988092 |
LOG10
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the logarithm of a value to base 10. The input can be any numeric value, the return value is always a double. Logs of 0 and negative numbers return null
as well as a warning.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW d = 1000.0 | EVAL s = LOG10(d)
d: double | s:double |
---|---|
1000.0 |
3.0 |
PI
editSyntax
Parameters
Description
Returns Pi, the ratio of a circle’s circumference to its diameter.
Supported types
result |
---|
double |
Example
ROW PI()
PI():double |
---|
3.141592653589793 |
POW
editSyntax
Parameters
-
base
-
Numeric expression for the base. If
null
, the function returnsnull
. -
exponent
-
Numeric expression for the exponent. If
null
, the function returnsnull
.
Description
Returns the value of base
raised to the power of exponent
.
It is still possible to overflow a double result here; in that case, null will be returned.
Supported types
base | exponent | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
double |
unsigned_long |
double |
integer |
double |
double |
integer |
integer |
double |
integer |
long |
double |
integer |
unsigned_long |
double |
long |
double |
double |
long |
integer |
double |
long |
long |
double |
long |
unsigned_long |
double |
unsigned_long |
double |
double |
unsigned_long |
integer |
double |
unsigned_long |
long |
double |
unsigned_long |
unsigned_long |
double |
Examples
ROW base = 2.0, exponent = 2 | EVAL result = POW(base, exponent)
base:double | exponent:integer | result:double |
---|---|---|
2.0 |
2 |
4.0 |
The exponent can be a fraction, which is similar to performing a root.
For example, the exponent of 0.5
will give the square root of the base:
ROW base = 4, exponent = 0.5 | EVAL s = POW(base, exponent)
base:integer | exponent:double | s:double |
---|---|---|
4 |
0.5 |
2.0 |
ROUND
editSyntax
Parameters
-
number
-
The numeric value to round. If
null
, the function returnsnull
. -
decimals
-
The number of decimal places to round to. Defaults to 0. If
null
, the function returnsnull
.
Description
Rounds a number to the specified number of decimal places. Defaults to 0, which returns the nearest integer. If the precision is a negative number, rounds to the number of digits left of the decimal point.
Supported types
number | decimals | result |
---|---|---|
double |
integer |
double |
double |
double |
|
integer |
integer |
integer |
integer |
integer |
|
long |
integer |
long |
long |
long |
|
unsigned_long |
unsigned_long |
Example
FROM employees | KEEP first_name, last_name, height | EVAL height_ft = ROUND(height * 3.281, 1)
first_name:keyword | last_name:keyword | height:double | height_ft:double |
---|---|---|---|
Arumugam |
Ossenbruggen |
2.1 |
6.9 |
Kwee |
Schusler |
2.1 |
6.9 |
Saniya |
Kalloufi |
2.1 |
6.9 |
SIGNUM
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the sign of the given number. It returns -1
for negative numbers, 0
for 0
and 1
for positive numbers.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW d = 100.0 | EVAL s = SIGNUM(d)
d: double | s:double |
---|---|
100 |
1.0 |
SIN
editSyntax
Parameters
-
angle
-
An angle, in radians. If
null
, the function returnsnull
.
Description
Returns the sine of an angle.
Supported types
angle | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL sin=SIN(a)
a:double | sin:double |
---|---|
1.8 |
0.9738476308781951 |
SINH
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the hyperbolic sine of a number.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL sinh=SINH(a)
a:double | sinh:double |
---|---|
1.8 |
2.94217428809568 |
SQRT
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the square root of a number. The input can be any numeric value, the return value is always a double. Square roots of negative numbers and infinities are null.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW d = 100.0 | EVAL s = SQRT(d)
d: double | s:double |
---|---|
100.0 |
10.0 |
TAN
editSyntax
Parameters
-
angle
-
An angle, in radians. If
null
, the function returnsnull
.
Description
Returns the tangent of an angle.
Supported types
angle | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL tan=TAN(a)
a:double | tan:double |
---|---|
1.8 |
-4.286261674628062 |
TANH
editSyntax
Parameters
-
number
-
Numeric expression. If
null
, the function returnsnull
.
Description
Returns the hyperbolic tangent of a number.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=1.8 | EVAL tanh=TANH(a)
a:double | tanh:double |
---|---|
1.8 |
0.9468060128462683 |
TAU
editSyntax
Parameters
Description
Returns the ratio of a circle’s circumference to its radius.
Supported types
result |
---|
double |
Example
ROW TAU()
TAU():double |
---|
6.283185307179586 |
ES|QL Full-text search functions
editES|QL supports these full-text search functions:
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
MATCH
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
QSTR
MATCH
editDo not use on production environments. This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Syntax
Parameters
-
field
- Field that the query will target.
-
query
- Text you wish to find in the provided field.
Description
Performs a match query on the specified field. Returns true if the provided query matches the row.
Supported types
field | query | result |
---|---|---|
keyword |
keyword |
boolean |
keyword |
text |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
Example
FROM books | WHERE MATCH(author, "Faulkner") | KEEP book_no, author | SORT book_no | LIMIT 5;
book_no:keyword | author:text |
---|---|
2378 |
[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott] |
2713 |
William Faulkner |
2847 |
Colleen Faulkner |
2883 |
William Faulkner |
3293 |
Danny Faulkner |
QSTR
editDo not use on production environments. This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Syntax
Parameters
-
query
- Query string in Lucene query string format.
Description
Performs a query string query. Returns true if the provided query string matches the row.
Supported types
query | result |
---|---|
keyword |
boolean |
text |
boolean |
Example
FROM books | WHERE QSTR("author: Faulkner") | KEEP book_no, author | SORT book_no | LIMIT 5;
book_no:keyword | author:text |
---|---|
2378 |
[Carol Faulkner, Holly Byers Ochoa, Lucretia Mott] |
2713 |
William Faulkner |
2847 |
Colleen Faulkner |
2883 |
William Faulkner |
3293 |
Danny Faulkner |
ES|QL spatial functions
editES|QL supports these spatial functions:
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_INTERSECTS
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_DISJOINT
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_CONTAINS
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_WITHIN
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_X
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_Y
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
ST_DISTANCE
ST_INTERSECTS
editSyntax
Parameters
-
geomA
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. -
geomB
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. The second parameter must also have the same coordinate system as the first. This means it is not possible to combinegeo_*
andcartesian_*
parameters.
Description
Returns true if two geometries intersect. They intersect if they have any point in common, including their interior points (points along lines or within polygons). This is the inverse of the ST_DISJOINT function. In mathematical terms: ST_Intersects(A, B) ⇔ A ⋂ B ≠ ∅
Supported types
geomA | geomB | result |
---|---|---|
cartesian_point |
cartesian_point |
boolean |
cartesian_point |
cartesian_shape |
boolean |
cartesian_shape |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
geo_point |
geo_point |
boolean |
geo_point |
geo_shape |
boolean |
geo_shape |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
Example
FROM airports | WHERE ST_INTERSECTS(location, TO_GEOSHAPE("POLYGON((42 14, 43 14, 43 15, 42 15, 42 14))"))
abbrev:keyword | city:keyword | city_location:geo_point | country:keyword | location:geo_point | name:text | scalerank:i | type:k |
---|---|---|---|---|---|---|---|
HOD |
Al Ḩudaydah |
POINT(42.9511 14.8022) |
Yemen |
POINT(42.97109630194 14.7552534413725) |
Hodeidah Int’l |
9 |
mid |
ST_DISJOINT
editSyntax
Parameters
-
geomA
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. -
geomB
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. The second parameter must also have the same coordinate system as the first. This means it is not possible to combinegeo_*
andcartesian_*
parameters.
Description
Returns whether the two geometries or geometry columns are disjoint. This is the inverse of the ST_INTERSECTS function. In mathematical terms: ST_Disjoint(A, B) ⇔ A ⋂ B = ∅
Supported types
geomA | geomB | result |
---|---|---|
cartesian_point |
cartesian_point |
boolean |
cartesian_point |
cartesian_shape |
boolean |
cartesian_shape |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
geo_point |
geo_point |
boolean |
geo_point |
geo_shape |
boolean |
geo_shape |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
Example
FROM airport_city_boundaries | WHERE ST_DISJOINT(city_boundary, TO_GEOSHAPE("POLYGON((-10 -60, 120 -60, 120 60, -10 60, -10 -60))")) | KEEP abbrev, airport, region, city, city_location
abbrev:keyword | airport:text | region:text | city:keyword | city_location:geo_point |
---|---|---|---|---|
ACA |
General Juan N Alvarez Int’l |
Acapulco de Juárez |
Acapulco de Juárez |
POINT (-99.8825 16.8636) |
ST_CONTAINS
editSyntax
Parameters
-
geomA
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. -
geomB
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. The second parameter must also have the same coordinate system as the first. This means it is not possible to combinegeo_*
andcartesian_*
parameters.
Description
Returns whether the first geometry contains the second geometry. This is the inverse of the ST_WITHIN function.
Supported types
geomA | geomB | result |
---|---|---|
cartesian_point |
cartesian_point |
boolean |
cartesian_point |
cartesian_shape |
boolean |
cartesian_shape |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
geo_point |
geo_point |
boolean |
geo_point |
geo_shape |
boolean |
geo_shape |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
Example
FROM airport_city_boundaries | WHERE ST_CONTAINS(city_boundary, TO_GEOSHAPE("POLYGON((109.35 18.3, 109.45 18.3, 109.45 18.4, 109.35 18.4, 109.35 18.3))")) | KEEP abbrev, airport, region, city, city_location
abbrev:keyword | airport:text | region:text | city:keyword | city_location:geo_point |
---|---|---|---|---|
SYX |
Sanya Phoenix Int’l |
天涯区 |
Sanya |
POINT(109.5036 18.2533) |
ST_WITHIN
editSyntax
Parameters
-
geomA
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. -
geomB
-
Expression of type
geo_point
,cartesian_point
,geo_shape
orcartesian_shape
. Ifnull
, the function returnsnull
. The second parameter must also have the same coordinate system as the first. This means it is not possible to combinegeo_*
andcartesian_*
parameters.
Description
Returns whether the first geometry is within the second geometry. This is the inverse of the ST_CONTAINS function.
Supported types
geomA | geomB | result |
---|---|---|
cartesian_point |
cartesian_point |
boolean |
cartesian_point |
cartesian_shape |
boolean |
cartesian_shape |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
geo_point |
geo_point |
boolean |
geo_point |
geo_shape |
boolean |
geo_shape |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
Example
FROM airport_city_boundaries | WHERE ST_WITHIN(city_boundary, TO_GEOSHAPE("POLYGON((109.1 18.15, 109.6 18.15, 109.6 18.65, 109.1 18.65, 109.1 18.15))")) | KEEP abbrev, airport, region, city, city_location
abbrev:keyword | airport:text | region:text | city:keyword | city_location:geo_point |
---|---|---|---|---|
SYX |
Sanya Phoenix Int’l |
天涯区 |
Sanya |
POINT(109.5036 18.2533) |
ST_X
editSyntax
Parameters
-
point
-
Expression of type
geo_point
orcartesian_point
. Ifnull
, the function returnsnull
.
Description
Extracts the x
coordinate from the supplied point. If the points is of type geo_point
this is equivalent to extracting the longitude
value.
Supported types
point | result |
---|---|
cartesian_point |
double |
geo_point |
double |
Example
ROW point = TO_GEOPOINT("POINT(42.97109629958868 14.7552534006536)") | EVAL x = ST_X(point), y = ST_Y(point)
point:geo_point | x:double | y:double |
---|---|---|
POINT(42.97109629958868 14.7552534006536) |
42.97109629958868 |
14.7552534006536 |
ST_Y
editSyntax
Parameters
-
point
-
Expression of type
geo_point
orcartesian_point
. Ifnull
, the function returnsnull
.
Description
Extracts the y
coordinate from the supplied point. If the points is of type geo_point
this is equivalent to extracting the latitude
value.
Supported types
point | result |
---|---|
cartesian_point |
double |
geo_point |
double |
Example
ROW point = TO_GEOPOINT("POINT(42.97109629958868 14.7552534006536)") | EVAL x = ST_X(point), y = ST_Y(point)
point:geo_point | x:double | y:double |
---|---|---|
POINT(42.97109629958868 14.7552534006536) |
42.97109629958868 |
14.7552534006536 |
ST_DISTANCE
editSyntax
Parameters
-
geomA
-
Expression of type
geo_point
orcartesian_point
. Ifnull
, the function returnsnull
. -
geomB
-
Expression of type
geo_point
orcartesian_point
. Ifnull
, the function returnsnull
. The second parameter must also have the same coordinate system as the first. This means it is not possible to combinegeo_point
andcartesian_point
parameters.
Description
Computes the distance between two points. For cartesian geometries, this is the pythagorean distance in the same units as the original coordinates. For geographic geometries, this is the circular distance along the great circle in meters.
Supported types
geomA | geomB | result |
---|---|---|
cartesian_point |
cartesian_point |
double |
geo_point |
geo_point |
double |
Example
FROM airports | WHERE abbrev == "CPH" | EVAL distance = ST_DISTANCE(location, city_location) | KEEP abbrev, name, location, city_location, distance
abbrev:k | name:text | location:geo_point | city_location:geo_point | distance:d |
---|---|---|---|---|
CPH |
Copenhagen |
POINT(12.6493508684508 55.6285017221528) |
POINT(12.5683 55.6761) |
7339.573896618216 |
ES|QL string functions
editES|QL supports these string functions:
BIT_LENGTH
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Returns the bit length of a string.
All strings are in UTF-8, so a single character can use multiple bytes.
Supported types
string | result |
---|---|
keyword |
integer |
text |
integer |
Example
FROM airports | WHERE country == "India" | KEEP city | EVAL fn_length=LENGTH(city), fn_bit_length = BIT_LENGTH(city)
city:keyword | fn_length:integer | fn_bit_length:integer |
---|---|---|
Agwār |
5 |
48 |
Ahmedabad |
9 |
72 |
Bangalore |
9 |
72 |
BYTE_LENGTH
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Returns the byte length of a string.
All strings are in UTF-8, so a single character can use multiple bytes.
Supported types
string | result |
---|---|
keyword |
integer |
text |
integer |
Example
FROM airports | WHERE country == "India" | KEEP city | EVAL fn_length=LENGTH(city), fn_byte_length = BYTE_LENGTH(city)
city:keyword | fn_length:integer | fn_byte_length:integer |
---|---|---|
Agwār |
5 |
6 |
Ahmedabad |
9 |
9 |
Bangalore |
9 |
9 |
CONCAT
editSyntax
Parameters
-
string1
- Strings to concatenate.
-
string2
- Strings to concatenate.
Description
Concatenates two or more strings.
Supported types
string1 | string2 | result |
---|---|---|
keyword |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
keyword |
text |
text |
keyword |
Example
FROM employees | KEEP first_name, last_name | EVAL fullname = CONCAT(first_name, " ", last_name)
first_name:keyword | last_name:keyword | fullname:keyword |
---|---|---|
Alejandro |
McAlpine |
Alejandro McAlpine |
Amabile |
Gomatam |
Amabile Gomatam |
Anneke |
Preusig |
Anneke Preusig |
ENDS_WITH
editSyntax
Parameters
-
str
-
String expression. If
null
, the function returnsnull
. -
suffix
-
String expression. If
null
, the function returnsnull
.
Description
Returns a boolean that indicates whether a keyword string ends with another string.
Supported types
str | suffix | result |
---|---|---|
keyword |
keyword |
boolean |
keyword |
text |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
Example
FROM employees | KEEP last_name | EVAL ln_E = ENDS_WITH(last_name, "d")
last_name:keyword | ln_E:boolean |
---|---|
Awdeh |
false |
Azuma |
false |
Baek |
false |
Bamford |
true |
Bernatsky |
false |
FROM_BASE64
editSyntax
Parameters
-
string
- A base64 string.
Description
Decode a base64 string.
Supported types
string | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
row a = "ZWxhc3RpYw==" | eval d = from_base64(a)
a:keyword | d:keyword |
---|---|
ZWxhc3RpYw== |
elastic |
LEFT
editSyntax
Parameters
-
string
- The string from which to return a substring.
-
length
- The number of characters to return.
Description
Returns the substring that extracts length chars from string starting from the left.
Supported types
string | length | result |
---|---|---|
keyword |
integer |
keyword |
text |
integer |
keyword |
Example
FROM employees | KEEP last_name | EVAL left = LEFT(last_name, 3) | SORT last_name ASC | LIMIT 5
last_name:keyword | left:keyword |
---|---|
Awdeh |
Awd |
Azuma |
Azu |
Baek |
Bae |
Bamford |
Bam |
Bernatsky |
Ber |
LENGTH
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Returns the character length of a string.
All strings are in UTF-8, so a single character can use multiple bytes.
Supported types
string | result |
---|---|
keyword |
integer |
text |
integer |
Example
FROM airports | WHERE country == "India" | KEEP city | EVAL fn_length = LENGTH(city)
city:keyword | fn_length:integer |
---|---|
Agwār |
5 |
Ahmedabad |
9 |
Bangalore |
9 |
LOCATE
editSyntax
Parameters
-
string
- An input string
-
substring
- A substring to locate in the input string
-
start
- The start index
Description
Returns an integer that indicates the position of a keyword substring within another string. Returns 0
if the substring cannot be found. Note that string positions start from 1
.
Supported types
string | substring | start | result |
---|---|---|---|
keyword |
keyword |
integer |
integer |
keyword |
keyword |
integer |
|
keyword |
text |
integer |
integer |
keyword |
text |
integer |
|
text |
keyword |
integer |
integer |
text |
keyword |
integer |
|
text |
text |
integer |
integer |
text |
text |
integer |
Example
row a = "hello" | eval a_ll = locate(a, "ll")
a:keyword | a_ll:integer |
---|---|
hello |
3 |
LTRIM
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Removes leading whitespaces from a string.
Supported types
string | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
ROW message = " some text ", color = " red " | EVAL message = LTRIM(message) | EVAL color = LTRIM(color) | EVAL message = CONCAT("'", message, "'") | EVAL color = CONCAT("'", color, "'")
message:keyword | color:keyword |
---|---|
'some text ' |
'red ' |
REPEAT
editSyntax
Parameters
-
string
- String expression.
-
number
- Number times to repeat.
Description
Returns a string constructed by concatenating string
with itself the specified number
of times.
Supported types
string | number | result |
---|---|---|
keyword |
integer |
keyword |
text |
integer |
keyword |
Example
ROW a = "Hello!" | EVAL triple_a = REPEAT(a, 3)
a:keyword | triple_a:keyword |
---|---|
Hello! |
Hello!Hello!Hello! |
REPLACE
editSyntax
Parameters
-
string
- String expression.
-
regex
- Regular expression.
-
newString
- Replacement string.
Description
The function substitutes in the string str
any match of the regular expression regex
with the replacement string newStr
.
Supported types
string | regex | newString | result |
---|---|---|---|
keyword |
keyword |
keyword |
keyword |
keyword |
keyword |
text |
keyword |
keyword |
text |
keyword |
keyword |
keyword |
text |
text |
keyword |
text |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
text |
text |
keyword |
keyword |
text |
text |
text |
keyword |
Example
This example replaces any occurrence of the word "World" with the word "Universe":
ROW str = "Hello World" | EVAL str = REPLACE(str, "World", "Universe") | KEEP str
str:keyword |
---|
Hello Universe |
REVERSE
editSyntax
Parameters
-
str
-
String expression. If
null
, the function returnsnull
.
Description
Returns a new string representing the input string in reverse order.
Supported types
str | result |
---|---|
keyword |
keyword |
text |
keyword |
Examples
ROW message = "Some Text" | EVAL message_reversed = REVERSE(message);
message:keyword | message_reversed:keyword |
---|---|
Some Text |
txeT emoS |
REVERSE
works with unicode, too! It keeps unicode grapheme clusters together during reversal.
ROW bending_arts = "💧🪨🔥💨" | EVAL bending_arts_reversed = REVERSE(bending_arts);
bending_arts:keyword | bending_arts_reversed:keyword |
---|---|
💧🪨🔥💨 |
💨🔥🪨💧 |
RIGHT
editSyntax
Parameters
-
string
- The string from which to returns a substring.
-
length
- The number of characters to return.
Description
Return the substring that extracts length chars from str starting from the right.
Supported types
string | length | result |
---|---|---|
keyword |
integer |
keyword |
text |
integer |
keyword |
Example
FROM employees | KEEP last_name | EVAL right = RIGHT(last_name, 3) | SORT last_name ASC | LIMIT 5
last_name:keyword | right:keyword |
---|---|
Awdeh |
deh |
Azuma |
uma |
Baek |
aek |
Bamford |
ord |
Bernatsky |
sky |
RTRIM
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Removes trailing whitespaces from a string.
Supported types
string | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
ROW message = " some text ", color = " red " | EVAL message = RTRIM(message) | EVAL color = RTRIM(color) | EVAL message = CONCAT("'", message, "'") | EVAL color = CONCAT("'", color, "'")
message:keyword | color:keyword |
---|---|
' some text' |
' red' |
SPACE
editSyntax
Parameters
-
number
- Number of spaces in result.
Description
Returns a string made of number
spaces.
Supported types
number | result |
---|---|
integer |
keyword |
Example
ROW message = CONCAT("Hello", SPACE(1), "World!");
message:keyword |
---|
Hello World! |
SPLIT
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
. -
delim
- Delimiter. Only single byte delimiters are currently supported.
Description
Split a single valued string into multiple strings.
Supported types
string | delim | result |
---|---|---|
keyword |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
keyword |
text |
text |
keyword |
Example
ROW words="foo;bar;baz;qux;quux;corge" | EVAL word = SPLIT(words, ";")
words:keyword | word:keyword |
---|---|
foo;bar;baz;qux;quux;corge |
[foo,bar,baz,qux,quux,corge] |
STARTS_WITH
editSyntax
Parameters
-
str
-
String expression. If
null
, the function returnsnull
. -
prefix
-
String expression. If
null
, the function returnsnull
.
Description
Returns a boolean that indicates whether a keyword string starts with another string.
Supported types
str | prefix | result |
---|---|---|
keyword |
keyword |
boolean |
keyword |
text |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
Example
FROM employees | KEEP last_name | EVAL ln_S = STARTS_WITH(last_name, "B")
last_name:keyword | ln_S:boolean |
---|---|
Awdeh |
false |
Azuma |
false |
Baek |
true |
Bamford |
true |
Bernatsky |
true |
SUBSTRING
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
. -
start
- Start position.
-
length
-
Length of the substring from the start position. Optional; if omitted, all positions after
start
are returned.
Description
Returns a substring of a string, specified by a start position and an optional length.
Supported types
string | start | length | result |
---|---|---|---|
keyword |
integer |
integer |
keyword |
text |
integer |
integer |
keyword |
Examples
This example returns the first three characters of every last name:
FROM employees | KEEP last_name | EVAL ln_sub = SUBSTRING(last_name, 1, 3)
last_name:keyword | ln_sub:keyword |
---|---|
Awdeh |
Awd |
Azuma |
Azu |
Baek |
Bae |
Bamford |
Bam |
Bernatsky |
Ber |
A negative start position is interpreted as being relative to the end of the string. This example returns the last three characters of of every last name:
FROM employees | KEEP last_name | EVAL ln_sub = SUBSTRING(last_name, -3, 3)
last_name:keyword | ln_sub:keyword |
---|---|
Awdeh |
deh |
Azuma |
uma |
Baek |
aek |
Bamford |
ord |
Bernatsky |
sky |
If length is omitted, substring returns the remainder of the string. This example returns all characters except for the first:
FROM employees | KEEP last_name | EVAL ln_sub = SUBSTRING(last_name, 2)
last_name:keyword | ln_sub:keyword |
---|---|
Awdeh |
wdeh |
Azuma |
zuma |
Baek |
aek |
Bamford |
amford |
Bernatsky |
ernatsky |
TO_BASE64
editSyntax
Parameters
-
string
- A string.
Description
Encode a string to a base64 string.
Supported types
string | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
row a = "elastic" | eval e = to_base64(a)
a:keyword | e:keyword |
---|---|
elastic |
ZWxhc3RpYw== |
TO_LOWER
editSyntax
Parameters
-
str
-
String expression. If
null
, the function returnsnull
.
Description
Returns a new string representing the input string converted to lower case.
Supported types
str | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
ROW message = "Some Text" | EVAL message_lower = TO_LOWER(message)
message:keyword | message_lower:keyword |
---|---|
Some Text |
some text |
TO_UPPER
editSyntax
Parameters
-
str
-
String expression. If
null
, the function returnsnull
.
Description
Returns a new string representing the input string converted to upper case.
Supported types
str | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
ROW message = "Some Text" | EVAL message_upper = TO_UPPER(message)
message:keyword | message_upper:keyword |
---|---|
Some Text |
SOME TEXT |
TRIM
editSyntax
Parameters
-
string
-
String expression. If
null
, the function returnsnull
.
Description
Removes leading and trailing whitespaces from a string.
Supported types
string | result |
---|---|
keyword |
keyword |
text |
keyword |
Example
ROW message = " some text ", color = " red " | EVAL message = TRIM(message) | EVAL color = TRIM(color)
message:s | color:s |
---|---|
some text |
red |
ES|QL type conversion functions
editES|QL supports implicit casting from string literals to certain data types. Refer to implicit casting for details.
ES|QL supports these type conversion functions:
-
TO_BOOLEAN
-
TO_CARTESIANPOINT
-
TO_CARTESIANSHAPE
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_DATEPERIOD
-
TO_DATETIME
-
TO_DEGREES
-
TO_DOUBLE
-
TO_GEOPOINT
-
TO_GEOSHAPE
-
TO_INTEGER
-
TO_IP
-
TO_LONG
-
TO_RADIANS
-
TO_STRING
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_TIMEDURATION
-
[preview]
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
TO_UNSIGNED_LONG
-
TO_VERSION
TO_BOOLEAN
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a boolean value. A string value of true will be case-insensitive converted to the Boolean true. For anything else, including the empty string, the function will return false. The numerical value of 0 will be converted to false, anything else will be converted to true.
Supported types
field | result |
---|---|
boolean |
boolean |
double |
boolean |
integer |
boolean |
keyword |
boolean |
long |
boolean |
text |
boolean |
unsigned_long |
boolean |
Example
ROW str = ["true", "TRuE", "false", "", "yes", "1"] | EVAL bool = TO_BOOLEAN(str)
str:keyword | bool:boolean |
---|---|
["true", "TRuE", "false", "", "yes", "1"] |
[true, true, false, false, false, false] |
TO_CARTESIANPOINT
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a cartesian_point
value. A string will only be successfully converted if it respects the WKT Point format.
Supported types
field | result |
---|---|
cartesian_point |
cartesian_point |
keyword |
cartesian_point |
text |
cartesian_point |
Example
ROW wkt = ["POINT(4297.11 -1475.53)", "POINT(7580.93 2272.77)"] | MV_EXPAND wkt | EVAL pt = TO_CARTESIANPOINT(wkt)
wkt:keyword | pt:cartesian_point |
---|---|
"POINT(4297.11 -1475.53)" |
POINT(4297.11 -1475.53) |
"POINT(7580.93 2272.77)" |
POINT(7580.93 2272.77) |
TO_CARTESIANSHAPE
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a cartesian_shape
value. A string will only be successfully converted if it respects the WKT format.
Supported types
field | result |
---|---|
cartesian_point |
cartesian_shape |
cartesian_shape |
cartesian_shape |
keyword |
cartesian_shape |
text |
cartesian_shape |
Example
ROW wkt = ["POINT(4297.11 -1475.53)", "POLYGON ((3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97))"] | MV_EXPAND wkt | EVAL geom = TO_CARTESIANSHAPE(wkt)
wkt:keyword | geom:cartesian_shape |
---|---|
"POINT(4297.11 -1475.53)" |
POINT(4297.11 -1475.53) |
"POLYGON 3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97" |
POLYGON 3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97 |
TO_DATEPERIOD
editSyntax
Parameters
-
field
- Input value. The input is a valid constant date period expression.
Description
Converts an input value into a date_period
value.
Supported types
field | result |
---|---|
date_period |
date_period |
keyword |
date_period |
text |
date_period |
Example
row x = "2024-01-01"::datetime | eval y = x + "3 DAYS"::date_period, z = x - to_dateperiod("3 days");
x:datetime | y:datetime | z:datetime |
---|---|---|
2024-01-01 |
2024-01-04 |
2023-12-29 |
TO_DATETIME
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a date value. A string will only be successfully converted if it’s respecting the format yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
. To convert dates in other formats, use DATE_PARSE
.
Note that when converting from nanosecond resolution to millisecond resolution with this function, the nanosecond date is truncated, not rounded.
Supported types
field | result |
---|---|
date |
date |
double |
date |
integer |
date |
keyword |
date |
long |
date |
text |
date |
unsigned_long |
date |
Examples
ROW string = ["1953-09-02T00:00:00.000Z", "1964-06-02T00:00:00.000Z", "1964-06-02 00:00:00"] | EVAL datetime = TO_DATETIME(string)
string:keyword | datetime:date |
---|---|
["1953-09-02T00:00:00.000Z", "1964-06-02T00:00:00.000Z", "1964-06-02 00:00:00"] |
[1953-09-02T00:00:00.000Z, 1964-06-02T00:00:00.000Z] |
Note that in this example, the last value in the source multi-valued field has not been converted. The reason being that if the date format is not respected, the conversion will result in a null value. When this happens a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:112: evaluation of [TO_DATETIME(string)] failed, treating result as null. "Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"java.lang.IllegalArgumentException: failed to parse date field [1964-06-02 00:00:00]
with format [yyyy-MM-dd'T'HH:mm:ss.SSS'Z']"
If the input parameter is of a numeric type, its value will be interpreted as milliseconds since the Unix epoch. For example:
ROW int = [0, 1] | EVAL dt = TO_DATETIME(int)
int:integer | dt:date |
---|---|
[0, 1] |
[1970-01-01T00:00:00.000Z, 1970-01-01T00:00:00.001Z] |
TO_DEGREES
editSyntax
Parameters
-
number
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts a number in radians to degrees.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW rad = [1.57, 3.14, 4.71] | EVAL deg = TO_DEGREES(rad)
rad:double | deg:double |
---|---|
[1.57, 3.14, 4.71] |
[89.95437383553924, 179.9087476710785, 269.86312150661774] |
TO_DOUBLE
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a double value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to double. Boolean true will be converted to double 1.0, false to 0.0.
Supported types
field | result |
---|---|
boolean |
double |
counter_double |
double |
counter_integer |
double |
counter_long |
double |
date |
double |
double |
double |
integer |
double |
keyword |
double |
long |
double |
text |
double |
unsigned_long |
double |
Example
ROW str1 = "5.20128E11", str2 = "foo" | EVAL dbl = TO_DOUBLE("520128000000"), dbl1 = TO_DOUBLE(str1), dbl2 = TO_DOUBLE(str2)
str1:keyword | str2:keyword | dbl:double | dbl1:double | dbl2:double |
---|---|---|---|---|
5.20128E11 |
foo |
5.20128E11 |
5.20128E11 |
null |
Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:115: evaluation of [TO_DOUBLE(str2)] failed, treating result as null. Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"java.lang.NumberFormatException: For input string: "foo""
TO_GEOPOINT
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a geo_point
value. A string will only be successfully converted if it respects the WKT Point format.
Supported types
field | result |
---|---|
geo_point |
geo_point |
keyword |
geo_point |
text |
geo_point |
Example
ROW wkt = "POINT(42.97109630194 14.7552534413725)" | EVAL pt = TO_GEOPOINT(wkt)
wkt:keyword | pt:geo_point |
---|---|
"POINT(42.97109630194 14.7552534413725)" |
POINT(42.97109630194 14.7552534413725) |
TO_GEOSHAPE
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a geo_shape
value. A string will only be successfully converted if it respects the WKT format.
Supported types
field | result |
---|---|
geo_point |
geo_shape |
geo_shape |
geo_shape |
keyword |
geo_shape |
text |
geo_shape |
Example
ROW wkt = "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" | EVAL geom = TO_GEOSHAPE(wkt)
wkt:keyword | geom:geo_shape |
---|---|
"POLYGON 30 10, 40 40, 20 40, 10 20, 30 10" |
POLYGON 30 10, 40 40, 20 40, 10 20, 30 10 |
TO_INTEGER
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to an integer value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to integer. Boolean true will be converted to integer 1, false to 0.
Supported types
field | result |
---|---|
boolean |
integer |
counter_integer |
integer |
date |
integer |
double |
integer |
integer |
integer |
keyword |
integer |
long |
integer |
text |
integer |
unsigned_long |
integer |
Example
ROW long = [5013792, 2147483647, 501379200000] | EVAL int = TO_INTEGER(long)
long:long | int:integer |
---|---|
[5013792, 2147483647, 501379200000] |
[5013792, 2147483647] |
Note that in this example, the last value of the multi-valued field cannot be converted as an integer. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:61: evaluation of [TO_INTEGER(long)] failed, treating result as null. Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"org.elasticsearch.xpack.esql.core.InvalidArgumentException: [501379200000] out of [integer] range"
TO_IP
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input string to an IP value.
Supported types
field | result |
---|---|
ip |
ip |
keyword |
ip |
text |
ip |
Example
ROW str1 = "1.1.1.1", str2 = "foo" | EVAL ip1 = TO_IP(str1), ip2 = TO_IP(str2) | WHERE CIDR_MATCH(ip1, "1.0.0.0/8")
str1:keyword | str2:keyword | ip1:ip | ip2:ip |
---|---|---|---|
1.1.1.1 |
foo |
1.1.1.1 |
null |
Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:68: evaluation of [TO_IP(str2)] failed, treating result as null. Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"java.lang.IllegalArgumentException: 'foo' is not an IP string literal."
TO_LONG
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to a long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to long. Boolean true will be converted to long 1, false to 0.
Supported types
field | result |
---|---|
boolean |
long |
counter_integer |
long |
counter_long |
long |
date |
long |
double |
long |
integer |
long |
keyword |
long |
long |
long |
text |
long |
unsigned_long |
long |
Example
ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo" | EVAL long1 = TO_LONG(str1), long2 = TO_LONG(str2), long3 = TO_LONG(str3)
str1:keyword | str2:keyword | str3:keyword | long1:long | long2:long | long3:long |
---|---|---|---|---|---|
2147483648 |
2147483648.2 |
foo |
2147483648 |
2147483648 |
null |
Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:113: evaluation of [TO_LONG(str3)] failed, treating result as null. Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"java.lang.NumberFormatException: For input string: "foo""
TO_RADIANS
editSyntax
Parameters
-
number
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts a number in degrees to radians.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW deg = [90.0, 180.0, 270.0] | EVAL rad = TO_RADIANS(deg)
deg:double | rad:double |
---|---|
[90.0, 180.0, 270.0] |
[1.5707963267948966, 3.141592653589793, 4.71238898038469] |
TO_STRING
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value into a string.
Supported types
field | result |
---|---|
boolean |
keyword |
cartesian_point |
keyword |
cartesian_shape |
keyword |
date |
keyword |
double |
keyword |
geo_point |
keyword |
geo_shape |
keyword |
integer |
keyword |
ip |
keyword |
keyword |
keyword |
long |
keyword |
text |
keyword |
unsigned_long |
keyword |
version |
keyword |
Examples
ROW a=10 | EVAL j = TO_STRING(a)
a:integer | j:keyword |
---|---|
10 |
"10" |
It also works fine on multivalued fields:
ROW a=[10, 9, 8] | EVAL j = TO_STRING(a)
a:integer | j:keyword |
---|---|
[10, 9, 8] |
["10", "9", "8"] |
TO_TIMEDURATION
editSyntax
Parameters
-
field
- Input value. The input is a valid constant time duration expression.
Description
Converts an input value into a time_duration
value.
Supported types
field | result |
---|---|
keyword |
time_duration |
text |
time_duration |
time_duration |
time_duration |
Example
row x = "2024-01-01"::datetime | eval y = x + "3 hours"::time_duration, z = x - to_timeduration("3 hours");
x:datetime | y:datetime | z:datetime |
---|---|---|
2024-01-01 |
2024-01-01T03:00:00.000Z |
2023-12-31T21:00:00.000Z |
TO_UNSIGNED_LONG
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input value to an unsigned long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to unsigned long. Boolean true will be converted to unsigned long 1, false to 0.
Supported types
field | result |
---|---|
boolean |
unsigned_long |
date |
unsigned_long |
double |
unsigned_long |
integer |
unsigned_long |
keyword |
unsigned_long |
long |
unsigned_long |
text |
unsigned_long |
unsigned_long |
unsigned_long |
Example
ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo" | EVAL long1 = TO_UNSIGNED_LONG(str1), long2 = TO_ULONG(str2), long3 = TO_UL(str3)
str1:keyword | str2:keyword | str3:keyword | long1:unsigned_long | long2:unsigned_long | long3:unsigned_long |
---|---|---|---|---|---|
2147483648 |
2147483648.2 |
foo |
2147483648 |
2147483648 |
null |
Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:
"Line 1:133: evaluation of [TO_UL(str3)] failed, treating result as null. Only first 20 failures recorded."
A following header will contain the failure reason and the offending value:
"java.lang.NumberFormatException: Character f is neither a decimal digit number, decimal point,
+ "nor "e" notation exponential mark."
TO_VERSION
editSyntax
Parameters
-
field
- Input value. The input can be a single- or multi-valued column or an expression.
Description
Converts an input string to a version value.
Supported types
field | result |
---|---|
keyword |
version |
text |
version |
version |
version |
Example
ROW v = TO_VERSION("1.2.3")
v:version |
---|
1.2.3 |
ES|QL multivalue functions
editES|QL supports these multivalue functions:
MV_APPEND
editSyntax
Parameters
-
field1
-
field2
Description
Concatenates values of two multi-value fields.
Supported types
field1 | field2 | result |
---|---|---|
boolean |
boolean |
boolean |
cartesian_point |
cartesian_point |
cartesian_point |
cartesian_shape |
cartesian_shape |
cartesian_shape |
date |
date |
date |
double |
double |
double |
geo_point |
geo_point |
geo_point |
geo_shape |
geo_shape |
geo_shape |
integer |
integer |
integer |
ip |
ip |
ip |
keyword |
keyword |
keyword |
long |
long |
long |
text |
text |
keyword |
version |
version |
version |
MV_AVG
editSyntax
Parameters
-
number
- Multivalue expression.
Description
Converts a multivalued field into a single valued field containing the average of all of the values.
Supported types
number | result |
---|---|
double |
double |
integer |
double |
long |
double |
unsigned_long |
double |
Example
ROW a=[3, 5, 1, 6] | EVAL avg_a = MV_AVG(a)
a:integer | avg_a:double |
---|---|
[3, 5, 1, 6] |
3.75 |
MV_CONCAT
editSyntax
Parameters
-
string
- Multivalue expression.
-
delim
- Delimiter.
Description
Converts a multivalued string expression into a single valued column containing the concatenation of all values separated by a delimiter.
Supported types
string | delim | result |
---|---|---|
keyword |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
keyword |
text |
text |
keyword |
Examples
ROW a=["foo", "zoo", "bar"] | EVAL j = MV_CONCAT(a, ", ")
a:keyword | j:keyword |
---|---|
["foo", "zoo", "bar"] |
"foo, zoo, bar" |
To concat non-string columns, call TO_STRING
first:
ROW a=[10, 9, 8] | EVAL j = MV_CONCAT(TO_STRING(a), ", ")
a:integer | j:keyword |
---|---|
[10, 9, 8] |
"10, 9, 8" |
MV_COUNT
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Converts a multivalued expression into a single valued column containing a count of the number of values.
Supported types
field | result |
---|---|
boolean |
integer |
cartesian_point |
integer |
cartesian_shape |
integer |
date |
integer |
double |
integer |
geo_point |
integer |
geo_shape |
integer |
integer |
integer |
ip |
integer |
keyword |
integer |
long |
integer |
text |
integer |
unsigned_long |
integer |
version |
integer |
Example
ROW a=["foo", "zoo", "bar"] | EVAL count_a = MV_COUNT(a)
a:keyword | count_a:integer |
---|---|
["foo", "zoo", "bar"] |
3 |
MV_DEDUPE
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Remove duplicate values from a multivalued field.
MV_DEDUPE
may, but won’t always, sort the values in the column.
Supported types
field | result |
---|---|
boolean |
boolean |
cartesian_point |
cartesian_point |
cartesian_shape |
cartesian_shape |
date |
date |
double |
double |
geo_point |
geo_point |
geo_shape |
geo_shape |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
version |
version |
Example
ROW a=["foo", "foo", "bar", "foo"] | EVAL dedupe_a = MV_DEDUPE(a)
a:keyword | dedupe_a:keyword |
---|---|
["foo", "foo", "bar", "foo"] |
["foo", "bar"] |
MV_FIRST
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Converts a multivalued expression into a single valued column containing the first value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT
.
The order that multivalued fields are read from
underlying storage is not guaranteed. It is frequently ascending, but don’t
rely on that. If you need the minimum value use MV_MIN
instead of
MV_FIRST
. MV_MIN
has optimizations for sorted values so there isn’t a
performance benefit to MV_FIRST
.
Supported types
field | result |
---|---|
boolean |
boolean |
cartesian_point |
cartesian_point |
cartesian_shape |
cartesian_shape |
date |
date |
double |
double |
geo_point |
geo_point |
geo_shape |
geo_shape |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
unsigned_long |
unsigned_long |
version |
version |
Example
ROW a="foo;bar;baz" | EVAL first_a = MV_FIRST(SPLIT(a, ";"))
a:keyword | first_a:keyword |
---|---|
foo;bar;baz |
"foo" |
MV_LAST
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Converts a multivalue expression into a single valued column containing the last value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT
.
The order that multivalued fields are read from
underlying storage is not guaranteed. It is frequently ascending, but don’t
rely on that. If you need the maximum value use MV_MAX
instead of
MV_LAST
. MV_MAX
has optimizations for sorted values so there isn’t a
performance benefit to MV_LAST
.
Supported types
field | result |
---|---|
boolean |
boolean |
cartesian_point |
cartesian_point |
cartesian_shape |
cartesian_shape |
date |
date |
double |
double |
geo_point |
geo_point |
geo_shape |
geo_shape |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
unsigned_long |
unsigned_long |
version |
version |
Example
ROW a="foo;bar;baz" | EVAL last_a = MV_LAST(SPLIT(a, ";"))
a:keyword | last_a:keyword |
---|---|
foo;bar;baz |
"baz" |
MV_MAX
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Converts a multivalued expression into a single valued column containing the maximum value.
Supported types
field | result |
---|---|
boolean |
boolean |
date |
date |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
unsigned_long |
unsigned_long |
version |
version |
Examples
ROW a=[3, 5, 1] | EVAL max_a = MV_MAX(a)
a:integer | max_a:integer |
---|---|
[3, 5, 1] |
5 |
It can be used by any column type, including keyword
columns. In that case it picks the last string, comparing their utf-8 representation byte by byte:
ROW a=["foo", "zoo", "bar"] | EVAL max_a = MV_MAX(a)
a:keyword | max_a:keyword |
---|---|
["foo", "zoo", "bar"] |
"zoo" |
MV_MEDIAN
editSyntax
Parameters
-
number
- Multivalue expression.
Description
Converts a multivalued field into a single valued field containing the median value.
Supported types
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Examples
ROW a=[3, 5, 1] | EVAL median_a = MV_MEDIAN(a)
a:integer | median_a:integer |
---|---|
[3, 5, 1] |
3 |
If the row has an even number of values for a column, the result will be the average of the middle two entries. If the column is not floating point, the average rounds down:
ROW a=[3, 7, 1, 6] | EVAL median_a = MV_MEDIAN(a)
a:integer | median_a:integer |
---|---|
[3, 7, 1, 6] |
4 |
MV_MEDIAN_ABSOLUTE_DEVIATION
editSyntax
Parameters
-
number
- Multivalue expression.
Description
Converts a multivalued field into a single valued field containing the median absolute deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X
, the median absolute deviation is median(|median(X) - X|)
.
If the field has an even number of values, the medians will be calculated as the average of the middle two values. If the value is not a floating point number, the averages are rounded towards 0.
Supported types
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Example
ROW values = [0, 2, 5, 6] | EVAL median_absolute_deviation = MV_MEDIAN_ABSOLUTE_DEVIATION(values), median = MV_MEDIAN(values)
values:integer | median_absolute_deviation:integer | median:integer |
---|---|---|
[0, 2, 5, 6] |
2 |
3 |
MV_MIN
editSyntax
Parameters
-
field
- Multivalue expression.
Description
Converts a multivalued expression into a single valued column containing the minimum value.
Supported types
field | result |
---|---|
boolean |
boolean |
date |
date |
double |
double |
integer |
integer |
ip |
ip |
keyword |
keyword |
long |
long |
text |
keyword |
unsigned_long |
unsigned_long |
version |
version |
Examples
ROW a=[2, 1] | EVAL min_a = MV_MIN(a)
a:integer | min_a:integer |
---|---|
[2, 1] |
1 |
It can be used by any column type, including keyword
columns. In that case, it picks the first string, comparing their utf-8 representation byte by byte:
ROW a=["foo", "bar"] | EVAL min_a = MV_MIN(a)
a:keyword | min_a:keyword |
---|---|
["foo", "bar"] |
"bar" |
MV_PSERIES_WEIGHTED_SUM
editSyntax
Parameters
-
number
- Multivalue expression.
-
p
- It is a constant number that represents the p parameter in the P-Series. It impacts every element’s contribution to the weighted sum.
Description
Converts a multivalued expression into a single-valued column by multiplying every element on the input list by its corresponding term in P-Series and computing the sum.
Supported types
number | p | result |
---|---|---|
double |
double |
double |
Example
ROW a = [70.0, 45.0, 21.0, 21.0, 21.0] | EVAL sum = MV_PSERIES_WEIGHTED_SUM(a, 1.5) | KEEP sum
sum:double |
---|
94.45465156212452 |
MV_SLICE
editSyntax
Parameters
-
field
-
Multivalue expression. If
null
, the function returnsnull
. -
start
-
Start position. If
null
, the function returnsnull
. The start argument can be negative. An index of -1 is used to specify the last value in the list. -
end
-
End position(included). Optional; if omitted, the position at
start
is returned. The end argument can be negative. An index of -1 is used to specify the last value in the list.
Description
Returns a subset of the multivalued field using the start and end index values. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT
or MV_SORT
.
The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that.
Supported types
field | start | end | result |
---|---|---|---|
boolean |
integer |
integer |
boolean |
cartesian_point |
integer |
integer |
cartesian_point |
cartesian_shape |
integer |
integer |
cartesian_shape |
date |
integer |
integer |
date |
double |
integer |
integer |
double |
geo_point |
integer |
integer |
geo_point |
geo_shape |
integer |
integer |
geo_shape |
integer |
integer |
integer |
integer |
ip |
integer |
integer |
ip |
keyword |
integer |
integer |
keyword |
long |
integer |
integer |
long |
text |
integer |
integer |
keyword |
version |
integer |
integer |
version |
Examples
row a = [1, 2, 2, 3] | eval a1 = mv_slice(a, 1), a2 = mv_slice(a, 2, 3)
a:integer | a1:integer | a2:integer |
---|---|---|
[1, 2, 2, 3] |
2 |
[2, 3] |
row a = [1, 2, 2, 3] | eval a1 = mv_slice(a, -2), a2 = mv_slice(a, -3, -1)
a:integer | a1:integer | a2:integer |
---|---|---|
[1, 2, 2, 3] |
2 |
[2, 2, 3] |
MV_SORT
editSyntax
Parameters
-
field
-
Multivalue expression. If
null
, the function returnsnull
. -
order
- Sort order. The valid options are ASC and DESC, the default is ASC.
Description
Sorts a multivalued field in lexicographical order.
Supported types
field | order | result |
---|---|---|
boolean |
keyword |
boolean |
date |
keyword |
date |
double |
keyword |
double |
integer |
keyword |
integer |
ip |
keyword |
ip |
keyword |
keyword |
keyword |
long |
keyword |
long |
text |
keyword |
keyword |
version |
keyword |
version |
Example
ROW a = [4, 2, -3, 2] | EVAL sa = mv_sort(a), sd = mv_sort(a, "DESC")
a:integer | sa:integer | sd:integer |
---|---|---|
[4, 2, -3, 2] |
[-3, 2, 2, 4] |
[4, 2, 2, -3] |
MV_SUM
editSyntax
Parameters
-
number
- Multivalue expression.
Description
Converts a multivalued field into a single valued field containing the sum of all of the values.
Supported types
number | result |
---|---|
double |
double |
integer |
integer |
long |
long |
unsigned_long |
unsigned_long |
Example
ROW a=[3, 5, 6] | EVAL sum_a = MV_SUM(a)
a:integer | sum_a:integer |
---|---|
[3, 5, 6] |
14 |
MV_ZIP
editSyntax
Parameters
-
string1
- Multivalue expression.
-
string2
- Multivalue expression.
-
delim
-
Delimiter. Optional; if omitted,
,
is used as a default delimiter.
Description
Combines the values from two multivalued fields with a delimiter that joins them together.
Supported types
string1 | string2 | delim | result |
---|---|---|---|
keyword |
keyword |
keyword |
keyword |
keyword |
keyword |
text |
keyword |
keyword |
keyword |
keyword |
|
keyword |
text |
keyword |
keyword |
keyword |
text |
text |
keyword |
keyword |
text |
keyword |
|
text |
keyword |
keyword |
keyword |
text |
keyword |
text |
keyword |
text |
keyword |
keyword |
|
text |
text |
keyword |
keyword |
text |
text |
text |
keyword |
text |
text |
keyword |
Example
ROW a = ["x", "y", "z"], b = ["1", "2"] | EVAL c = mv_zip(a, b, "-") | KEEP a, b, c
a:keyword | b:keyword | c:keyword |
---|---|---|
[x, y, z] |
[1 ,2] |
[x-1, y-2, z] |
ES|QL operators
editBoolean operators for comparing against one or multiple expressions.
Binary operators
editEquality
editCheck if two fields are equal. If either field is multivalued then
the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
boolean |
boolean |
boolean |
cartesian_point |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
geo_point |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Inequality !=
editCheck if two fields are unequal. If either field is multivalued then
the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
boolean |
boolean |
boolean |
cartesian_point |
cartesian_point |
boolean |
cartesian_shape |
cartesian_shape |
boolean |
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
geo_point |
geo_point |
boolean |
geo_shape |
geo_shape |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Less than <
editCheck if one field is less than another. If either field is multivalued
then the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Less than or equal to <=
editCheck if one field is less than or equal to another. If either field is multivalued
then the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Greater than >
editCheck if one field is greater than another. If either field is multivalued
then the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Greater than or equal to >=
editCheck if one field is greater than or equal to another. If either field is multivalued
then the result is null
.
This is pushed to the underlying search index if one side of the comparison is constant
and the other side is a field in the index that has both an index
and doc_values
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date |
boolean |
double |
double |
boolean |
double |
integer |
boolean |
double |
long |
boolean |
integer |
double |
boolean |
integer |
integer |
boolean |
integer |
long |
boolean |
ip |
ip |
boolean |
keyword |
keyword |
boolean |
keyword |
text |
boolean |
long |
double |
boolean |
long |
integer |
boolean |
long |
long |
boolean |
text |
keyword |
boolean |
text |
text |
boolean |
unsigned_long |
unsigned_long |
boolean |
version |
version |
boolean |
Add +
editAdd two numbers together. If either field is multivalued
then the result is null
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date_period |
date |
date |
time_duration |
date |
date_period |
date |
date |
date_period |
date_period |
date_period |
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
integer |
integer |
long |
long |
long |
double |
double |
long |
integer |
long |
long |
long |
long |
time_duration |
date |
date |
time_duration |
time_duration |
time_duration |
unsigned_long |
unsigned_long |
unsigned_long |
Subtract -
editSubtract one number from another. If either field is multivalued
then the result is null
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
date |
date_period |
date |
date |
time_duration |
date |
date_period |
date_period |
date_period |
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
integer |
integer |
long |
long |
long |
double |
double |
long |
integer |
long |
long |
long |
long |
time_duration |
time_duration |
time_duration |
unsigned_long |
unsigned_long |
unsigned_long |
Multiply *
editMultiply two numbers together. If either field is multivalued
then the result is null
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
integer |
integer |
long |
long |
long |
double |
double |
long |
integer |
long |
long |
long |
long |
unsigned_long |
unsigned_long |
unsigned_long |
Divide /
editDivide one number by another. If either field is multivalued
then the result is null
.
Division of two integer types will yield an integer result, rounding towards 0.
If you need floating point division, Cast (::)
one of the arguments to a DOUBLE
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
integer |
integer |
long |
long |
long |
double |
double |
long |
integer |
long |
long |
long |
long |
unsigned_long |
unsigned_long |
unsigned_long |
Modulus %
editDivide one number by another and return the remainder. If either field is multivalued
then the result is null
.
Supported types:
Supported types
lhs | rhs | result |
---|---|---|
double |
double |
double |
double |
integer |
double |
double |
long |
double |
integer |
double |
double |
integer |
integer |
integer |
integer |
long |
long |
long |
double |
double |
long |
integer |
long |
long |
long |
long |
unsigned_long |
unsigned_long |
unsigned_long |
Unary operators
editThe only unary operators is negation (-
):
Supported types:
Supported types
field | result |
---|---|
date_period |
date_period |
double |
double |
integer |
integer |
long |
long |
time_duration |
time_duration |
Logical operators
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 |
Cast (::)
editThe ::
operator provides a convenient alternative syntax to the TO_<type>
conversion functions.
ROW ver = CONCAT(("0"::INT + 1)::STRING, ".2.3")::VERSION
ver:version |
---|
1.2.3 |
IN
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 |
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.
Supported types
str | pattern | result |
---|---|---|
keyword |
keyword |
boolean |
text |
text |
boolean |
FROM employees | WHERE first_name LIKE """?b*""" | KEEP first_name, last_name
first_name:keyword | last_name:keyword |
---|---|
Ebbe |
Callaway |
Eberhardt |
Terkki |
Matching the exact characters *
and .
will require escaping.
The escape character is backslash \
. Since also backslash is a special character in string literals,
it will require further escaping.
ROW message = "foo * bar" | WHERE message LIKE "foo \\* bar"
To reduce the overhead of escaping, we suggest using triple quotes strings """
ROW message = "foo * bar" | WHERE message LIKE """foo \* bar"""
RLIKE
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.
Supported types
str | pattern | result |
---|---|---|
keyword |
keyword |
boolean |
text |
text |
boolean |
FROM employees | WHERE first_name RLIKE """.leja.*""" | KEEP first_name, last_name
first_name:keyword | last_name:keyword |
---|---|
Alejandro |
McAlpine |
Matching special characters (eg. .
, *
, (
…) will require escaping.
The escape character is backslash \
. Since also backslash is a special character in string literals,
it will require further escaping.
ROW message = "foo ( bar" | WHERE message RLIKE "foo \\( bar"
To reduce the overhead of escaping, we suggest using triple quotes strings """
ROW message = "foo ( bar" | WHERE message RLIKE """foo \( bar"""