You're viewing version 2.5 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation . For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy .
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 .