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 sub-type of the number type, and it returns the actual type of T as the return type.

The SQL plugin supports the following functions.

Mathematical

FunctionSpecificationExample
absabs(number T) -> TSELECT abs(0.5) FROM my-index LIMIT 1
addadd(number T, number) -> TSELECT add(1, 5) FROM my-index LIMIT 1
cbrtcbrt(number T) -> TSELECT cbrt(0.5) FROM my-index LIMIT 1
ceilceil(number T) -> TSELECT ceil(0.5) FROM my-index LIMIT 1
convconv(string T, int a, int b) -> TSELECT CONV(‘12’, 10, 16), CONV(‘2C’, 16, 10), CONV(12, 10, 2), CONV(1111, 2, 10) FROM my-index LIMIT 1
crc32crc32(string T) -> TSELECT crc32(‘MySQL’) FROM my-index LIMIT 1
dividedivide(number T, number) -> TSELECT divide(1, 0.5) FROM my-index LIMIT 1
ee() -> doubleSELECT e() FROM my-index LIMIT 1
expexp(number T) -> TSELECT exp(0.5) FROM my-index LIMIT 1
expm1expm1(number T) -> TSELECT expm1(0.5) FROM my-index LIMIT 1
floorfloor(number T) -> TSELECT floor(0.5) AS Rounded_Down FROM my-index LIMIT 1
lnln(number T) -> doubleSELECT ln(10) FROM my-index LIMIT 1
loglog(number T) -> double or log(number T, number) -> doubleSELECT log(10) FROM my-index LIMIT 1
log2log2(number T) -> doubleSELECT log2(10) FROM my-index LIMIT 1
log10log10(number T) -> doubleSELECT log10(10) FROM my-index LIMIT 1
modmod(number T, number) -> TSELECT modulus(2, 3) FROM my-index LIMIT 1
multiplymultiply(number T, number) -> numberSELECT multiply(2, 3) FROM my-index LIMIT 1
pipi() -> doubleSELECT pi() FROM my-index LIMIT 1
powpow(number T) -> T or pow(number T, number) -> TSELECT pow(2, 3) FROM my-index LIMIT 1
powerpower(number T) -> T or power(number T, number) -> TSELECT power(2, 3) FROM my-index LIMIT 1
randrand() -> number or rand(number T) -> TSELECT rand(0.5) FROM my-index LIMIT 1
rintrint(number T) -> TSELECT rint(1.5) FROM my-index LIMIT 1
roundround(number T) -> TSELECT round(1.5) FROM my-index LIMIT 1
signsign(number T) -> TSELECT sign(1.5) FROM my-index LIMIT 1
signumsignum(number T) -> TSELECT signum(0.5) FROM my-index LIMIT 1
sqrtsqrt(number T) -> TSELECT sqrt(0.5) FROM my-index LIMIT 1
strcmpstrcmp(string T, string T) -> TSELECT strcmp(‘hello’, ‘hello’) FROM my-index LIMIT 1
subtractsubtract(number T, number) -> TSELECT subtract(3, 2) FROM my-index LIMIT 1
truncatetruncate(number T, number T) -> TSELECT truncate(56.78, 1) FROM my-index LIMIT 1
/number [op] number -> numberSELECT 1 / 100 FROM my-index LIMIT 1
%number [op] number -> numberSELECT 1 % 100 FROM my-index LIMIT 1

Trigonometric

FunctionSpecificationExample
acosacos(number T) -> doubleSELECT acos(0.5) FROM my-index LIMIT 1
asinasin(number T) -> doubleSELECT asin(0.5) FROM my-index LIMIT 1
atanatan(number T) -> doubleSELECT atan(0.5) FROM my-index LIMIT 1
atan2atan2(number T, number) -> doubleSELECT atan2(1, 0.5) FROM my-index LIMIT 1
coscos(number T) -> doubleSELECT cos(0.5) FROM my-index LIMIT 1
coshcosh(number T) -> doubleSELECT cosh(0.5) FROM my-index LIMIT 1
cotcot(number T) -> doubleSELECT cot(0.5) FROM my-index LIMIT 1
degreesdegrees(number T) -> doubleSELECT degrees(0.5) FROM my-index LIMIT 1
radiansradians(number T) -> doubleSELECT radians(0.5) FROM my-index LIMIT 1
sinsin(number T) -> doubleSELECT sin(0.5) FROM my-index LIMIT 1
sinhsinh(number T) -> doubleSELECT sinh(0.5) FROM my-index LIMIT 1
tantan(number T) -> doubleSELECT tan(0.5) FROM my-index LIMIT 1

Date and time

FunctionSpecificationExample
adddateadddate(date, INTERVAL expr unit) -> dateSELECT adddate(date(‘2020-08-26’), INTERVAL 1 hour) FROM my-index LIMIT 1
curdatecurdate() -> dateSELECT curdate() FROM my-index LIMIT 1
datedate(date) -> dateSELECT date() FROM my-index LIMIT 1
date_formatdate_format(date, string) -> string or date_format(date, string, string) -> stringSELECT date_format(date, ‘Y’) FROM my-index LIMIT 1
date_subdate_sub(date, INTERVAL expr unit) -> dateSELECT date_sub(date(‘2008-01-02’), INTERVAL 31 day) FROM my-index LIMIT 1
dayofmonthdayofmonth(date) -> integerSELECT dayofmonth(date) FROM my-index LIMIT 1
daynamedayname(date) -> stringSELECT dayname(date(‘2020-08-26’)) FROM my-index LIMIT 1
dayofyeardayofyear(date) -> integerSELECT dayofyear(date(‘2020-08-26’)) FROM my-index LIMIT 1
dayofweekdayofweek(date) -> integerSELECT dayofweek(date(‘2020-08-26’)) FROM my-index LIMIT 1
from_daysfrom_days(N) -> integerSELECT from_days(733687) FROM my-index LIMIT 1
hourhour(time) -> integerSELECT hour((time ‘01:02:03’)) FROM my-index LIMIT 1
maketimemaketime(integer, integer, integer) -> dateSELECT maketime(1, 2, 3) FROM my-index LIMIT 1
microsecondmicrosecond(expr) -> integerSELECT microsecond((time ‘01:02:03.123456’)) FROM my-index LIMIT 1
minuteminute(expr) -> integerSELECT minute((time ‘01:02:03’)) FROM my-index LIMIT 1
monthmonth(date) -> integerSELECT month(date) FROM my-index
monthnamemonthname(date) -> stringSELECT monthname(date) FROM my-index
nownow() -> dateSELECT now() FROM my-index LIMIT 1
quarterquarter(date) -> integerSELECT quarter(date(‘2020-08-26’)) FROM my-index LIMIT 1
secondsecond(time) -> integerSELECT second((time ‘01:02:03’)) FROM my-index LIMIT 1
subdatesubdate(date, INTERVAL expr unit) -> date, datetimeSELECT subdate(date(‘2008-01-02’), INTERVAL 31 day) FROM my-index LIMIT 1
timetime(expr) -> timeSELECT time(‘13:49:00’) FROM my-index LIMIT 1
time_to_sectime_to_sec(time) -> longSELECT time_to_sec(time ‘22:23:00’) FROM my-index LIMIT 1
timestamptimestamp(date) -> dateSELECT timestamp(date) FROM my-index LIMIT 1
to_daysto_days(date) -> longSELECT to_days(date ‘2008-10-07’) FROM my-index LIMIT 1
weekweek(date[mode]) -> integerSELECT week(date(‘2008-02-20’)) FROM my-index LIMIT 1
yearyear(date) -> integerSELECT year(date) FROM my-index LIMIT 1

String

FunctionSpecificationExample
asciiascii(string T) -> integerSELECT ascii(name.keyword) FROM my-index LIMIT 1
concatconcat(str1, str2) -> stringSELECT concat(‘hello’, ‘world’) FROM my-index LIMIT 1
concat_wsconcat_ws(separator, string, string…) -> stringSELECT concat_ws(“-“, “Tutorial”, “is”, “fun!”) FROM my-index LIMIT 1
leftleft(string T, integer) -> TSELECT left(‘hello’, 2) FROM my-index LIMIT 1
lengthlength(string) -> integerSELECT length(‘hello’) FROM my-index LIMIT 1
locatelocate(string, string, integer) -> integer or locate(string, string) -> INTEGERSELECT locate(‘o’, ‘hello’) FROM my-index LIMIT 1, SELECT locate(‘l’, ‘hello’, 3) FROM my-index LIMIT 1
replacereplace(string T, string, string) -> TSELECT replace(‘hello’, ‘l’, ‘x’) FROM my-index LIMIT 1
rightright(string T, integer) -> TSELECT right(‘hello’, 1) FROM my-index LIMIT 1
rtrimrtrim(string T) -> TSELECT rtrim(name.keyword) FROM my-index LIMIT 1
substringsubstring(string T, integer, integer) -> TSELECT substring(name.keyword, 2,5) FROM my-index LIMIT 1
trimtrim(string T) -> TSELECT trim(‘ hello’) FROM my-index LIMIT 1
upperupper(string T) -> TSELECT upper(‘helloworld’) FROM my-index LIMIT 1

Aggregate

FunctionSpecificationExample
avgavg(number T) -> TSELECT avg(2, 3) FROM my-index LIMIT 1
countcount(number T) -> TSELECT count(date) FROM my-index LIMIT 1
minmin(number T, number) -> TSELECT min(2, 3) FROM my-index LIMIT 1
showshow(string T) -> TSHOW TABLES LIKE my-index

Advanced

FunctionSpecificationExample
ifif(boolean, es_type, es_type) -> es_typeSELECT if(false, 0, 1) FROM my-index LIMIT 1, SELECT if(true, 0, 1) FROM my-index LIMIT 1
ifnullifnull(es_type, es_type) -> es_typeSELECT ifnull(‘hello’, 1) FROM my-index LIMIT 1, SELECT ifnull(null, 1) FROM my-index LIMIT 1
isnullisnull(es_type) -> integerSELECT isnull(null) FROM my-index LIMIT 1, SELECT isnull(1) FROM my-index LIMIT 1