Functions

Functions

You must enable fielddata in the document mapping for most string functions to work properly.

The specification shows the return type of the function with a generic type T as the argument. For example, abs(number T) -> T means that the function abs accepts a numerical argument of type T, which could be any subtype of the number type, and it returns the actual type of T as the return type.

The SQL plugin supports the following common functions shared across the SQL and PPL languages.

Mathematical

Function	Specification	Example
abs	`abs(number T) -> T`	`SELECT abs(0.5) FROM my-index LIMIT 1`
add	`add(number T, number) -> T`	`SELECT add(1, 5) FROM my-index LIMIT 1`
cbrt	`cbrt(number T) -> T`	`SELECT cbrt(0.5) FROM my-index LIMIT 1`
ceil	`ceil(number T) -> T`	`SELECT ceil(0.5) FROM my-index LIMIT 1`
conv	`conv(string T, int a, int b) -> T`	`SELECT CONV(‘12’, 10, 16), CONV(‘2C’, 16, 10), CONV(12, 10, 2), CONV(1111, 2, 10) FROM my-index LIMIT 1`
crc32	`crc32(string T) -> T`	`SELECT crc32(‘MySQL’) FROM my-index LIMIT 1`
divide	`divide(number T, number) -> T`	`SELECT divide(1, 0.5) FROM my-index LIMIT 1`
e	`e() -> double`	`SELECT e() FROM my-index LIMIT 1`
exp	`exp(number T) -> T`	`SELECT exp(0.5) FROM my-index LIMIT 1`
expm1	`expm1(number T) -> T`	`SELECT expm1(0.5) FROM my-index LIMIT 1`
floor	`floor(number T) -> T`	`SELECT floor(0.5) AS Rounded_Down FROM my-index LIMIT 1`
ln	`ln(number T) -> double`	`SELECT ln(10) FROM my-index LIMIT 1`
log	`log(number T) -> double` or `log(number T, number) -> double`	`SELECT log(10) FROM my-index LIMIT 1`
log2	`log2(number T) -> double`	`SELECT log2(10) FROM my-index LIMIT 1`
log10	`log10(number T) -> double`	`SELECT log10(10) FROM my-index LIMIT 1`
mod	`mod(number T, number) -> T`	`SELECT modulus(2, 3) FROM my-index LIMIT 1`
multiply	`multiply(number T, number) -> number`	`SELECT multiply(2, 3) FROM my-index LIMIT 1`
pi	`pi() -> double`	`SELECT pi() FROM my-index LIMIT 1`
pow	`pow(number T) -> T` or `pow(number T, number) -> T`	`SELECT pow(2, 3) FROM my-index LIMIT 1`
power	`power(number T) -> T` or `power(number T, number) -> T`	`SELECT power(2, 3) FROM my-index LIMIT 1`
rand	`rand() -> number` or `rand(number T) -> T`	`SELECT rand(0.5) FROM my-index LIMIT 1`
rint	`rint(number T) -> T`	`SELECT rint(1.5) FROM my-index LIMIT 1`
round	`round(number T) -> T`	`SELECT round(1.5) FROM my-index LIMIT 1`
sign	`sign(number T) -> T`	`SELECT sign(1.5) FROM my-index LIMIT 1`
signum	`signum(number T) -> T`	`SELECT signum(0.5) FROM my-index LIMIT 1`
sqrt	`sqrt(number T) -> T`	`SELECT sqrt(0.5) FROM my-index LIMIT 1`
strcmp	`strcmp(string T, string T) -> T`	`SELECT strcmp(‘hello’, ‘hello’) FROM my-index LIMIT 1`
subtract	`subtract(number T, number) -> T`	`SELECT subtract(3, 2) FROM my-index LIMIT 1`
truncate	`truncate(number T, number T) -> T`	`SELECT truncate(56.78, 1) FROM my-index LIMIT 1`
/	`number [op] number -> number`	`SELECT 1 / 100 FROM my-index LIMIT 1`
%	`number [op] number -> number`	`SELECT 1 % 100 FROM my-index LIMIT 1`

Trigonometric

Function	Specification	Example
acos	`acos(number T) -> double`	`SELECT acos(0.5) FROM my-index LIMIT 1`
asin	`asin(number T) -> double`	`SELECT asin(0.5) FROM my-index LIMIT 1`
atan	`atan(number T) -> double`	`SELECT atan(0.5) FROM my-index LIMIT 1`
atan2	`atan2(number T, number) -> double`	`SELECT atan2(1, 0.5) FROM my-index LIMIT 1`
cos	`cos(number T) -> double`	`SELECT cos(0.5) FROM my-index LIMIT 1`
cosh	`cosh(number T) -> double`	`SELECT cosh(0.5) FROM my-index LIMIT 1`
cot	`cot(number T) -> double`	`SELECT cot(0.5) FROM my-index LIMIT 1`
degrees	`degrees(number T) -> double`	`SELECT degrees(0.5) FROM my-index LIMIT 1`
radians	`radians(number T) -> double`	`SELECT radians(0.5) FROM my-index LIMIT 1`
sin	`sin(number T) -> double`	`SELECT sin(0.5) FROM my-index LIMIT 1`
sinh	`sinh(number T) -> double`	`SELECT sinh(0.5) FROM my-index LIMIT 1`
tan	`tan(number T) -> double`	`SELECT tan(0.5) FROM my-index LIMIT 1`

Date and time

Function	Specification	Example
adddate	`adddate(date, INTERVAL expr unit) -> date`	`SELECT adddate(date(‘2020-08-26’), INTERVAL 1 hour) FROM my-index LIMIT 1`
curdate	`curdate() -> date`	`SELECT curdate() FROM my-index LIMIT 1`
date	`date(date) -> date`	`SELECT date() FROM my-index LIMIT 1`
date_format	`date_format(date, string) -> string` or `date_format(date, string, string) -> string`	`SELECT date_format(date, ‘Y’) FROM my-index LIMIT 1`
date_sub	`date_sub(date, INTERVAL expr unit) -> date`	`SELECT date_sub(date(‘2008-01-02’), INTERVAL 31 day) FROM my-index LIMIT 1`
dayofmonth	`dayofmonth(date) -> integer`	`SELECT dayofmonth(date) FROM my-index LIMIT 1`
dayname	`dayname(date) -> string`	`SELECT dayname(date(‘2020-08-26’)) FROM my-index LIMIT 1`
dayofyear	`dayofyear(date) -> integer`	`SELECT dayofyear(date(‘2020-08-26’)) FROM my-index LIMIT 1`
dayofweek	`dayofweek(date) -> integer`	`SELECT dayofweek(date(‘2020-08-26’)) FROM my-index LIMIT 1`
from_days	`from_days(N) -> integer`	`SELECT from_days(733687) FROM my-index LIMIT 1`
hour	`hour(time) -> integer`	`SELECT hour((time ‘01:02:03’)) FROM my-index LIMIT 1`
maketime	`maketime(integer, integer, integer) -> date`	`SELECT maketime(1, 2, 3) FROM my-index LIMIT 1`
microsecond	`microsecond(expr) -> integer`	`SELECT microsecond((time ‘01:02:03.123456’)) FROM my-index LIMIT 1`
minute	`minute(expr) -> integer`	`SELECT minute((time ‘01:02:03’)) FROM my-index LIMIT 1`
month	`month(date) -> integer`	`SELECT month(date) FROM my-index`
monthname	`monthname(date) -> string`	`SELECT monthname(date) FROM my-index`
now	`now() -> date`	`SELECT now() FROM my-index LIMIT 1`
quarter	`quarter(date) -> integer`	`SELECT quarter(date(‘2020-08-26’)) FROM my-index LIMIT 1`
second	`second(time) -> integer`	`SELECT second((time ‘01:02:03’)) FROM my-index LIMIT 1`
subdate	`subdate(date, INTERVAL expr unit) -> date, datetime`	`SELECT subdate(date(‘2008-01-02’), INTERVAL 31 day) FROM my-index LIMIT 1`
time	`time(expr) -> time`	`SELECT time(‘13:49:00’) FROM my-index LIMIT 1`
time_to_sec	`time_to_sec(time) -> long`	`SELECT time_to_sec(time ‘22:23:00’) FROM my-index LIMIT 1`
timestamp	`timestamp(date) -> date`	`SELECT timestamp(date) FROM my-index LIMIT 1`
to_days	`to_days(date) -> long`	`SELECT to_days(date ‘2008-10-07’) FROM my-index LIMIT 1`
week	`week(date[mode]) -> integer`	`SELECT week(date(‘2008-02-20’)) FROM my-index LIMIT 1`
year	`year(date) -> integer`	`SELECT year(date) FROM my-index LIMIT 1`

String

Function	Specification	Example
ascii	`ascii(string T) -> integer`	`SELECT ascii(name.keyword) FROM my-index LIMIT 1`
concat	`concat(str1, str2) -> string`	`SELECT concat(‘hello’, ‘world’) FROM my-index LIMIT 1`
concat_ws	`concat_ws(separator, string, string…) -> string`	`SELECT concat_ws(“-“, “Tutorial”, “is”, “fun!”) FROM my-index LIMIT 1`
left	`left(string T, integer) -> T`	`SELECT left(‘hello’, 2) FROM my-index LIMIT 1`
length	`length(string) -> integer`	`SELECT length(‘hello’) FROM my-index LIMIT 1`
locate	`locate(string, string, integer) -> integer` or `locate(string, string) -> INTEGER`	`SELECT locate(‘o’, ‘hello’) FROM my-index LIMIT 1`, `SELECT locate(‘l’, ‘hello’, 3) FROM my-index LIMIT 1`
replace	`replace(string T, string, string) -> T`	`SELECT replace(‘hello’, ‘l’, ‘x’) FROM my-index LIMIT 1`
right	`right(string T, integer) -> T`	`SELECT right(‘hello’, 1) FROM my-index LIMIT 1`
rtrim	`rtrim(string T) -> T`	`SELECT rtrim(name.keyword) FROM my-index LIMIT 1`
substring	`substring(string T, integer, integer) -> T`	`SELECT substring(name.keyword, 2,5) FROM my-index LIMIT 1`
trim	`trim(string T) -> T`	`SELECT trim(‘ hello’) FROM my-index LIMIT 1`
upper	`upper(string T) -> T`	`SELECT upper(‘helloworld’) FROM my-index LIMIT 1`

Aggregate

Function	Specification	Example
avg	`avg(number T) -> T`	`SELECT avg(2, 3) FROM my-index LIMIT 1`
count	`count(number T) -> T`	`SELECT count(date) FROM my-index LIMIT 1`
min	`min(number T, number) -> T`	`SELECT min(2, 3) FROM my-index LIMIT 1`
show	`show(string T) -> T`	`SHOW TABLES LIKE my-index`

Advanced

Function	Specification	Example
if	`if(boolean, es_type, es_type) -> es_type`	`SELECT if(false, 0, 1) FROM my-index LIMIT 1`, `SELECT if(true, 0, 1) FROM my-index LIMIT 1`
ifnull	`ifnull(es_type, es_type) -> es_type`	`SELECT ifnull(‘hello’, 1) FROM my-index LIMIT 1`, `SELECT ifnull(null, 1) FROM my-index LIMIT 1`
isnull	`isnull(es_type) -> integer`	`SELECT isnull(null) FROM my-index LIMIT 1`, `SELECT isnull(1) FROM my-index LIMIT 1`

Relevance-based search (full-text search)

These functions are only available in the WHERE clause. For their descriptions and usage examples in SQL and PPL, see Full-text search.