You're viewing version 2.7 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
Functions marked with * are only available in SQL.
| Function | Specification | Example |
|---|---|---|
| adddate | adddate(date, INTERVAL expr unit) -> date | SELECT adddate(date('2020-08-26'), INTERVAL 1 hour) FROM my-index LIMIT 1 |
| addtime | addtime(date, date) -> date | SELECT addtime(date('2008-12-12'), date('2008-12-12')) |
| convert_tz | convert_tz(date, string, string) -> date | SELECT convert_tz('2008-12-25 05:30:00', '+00:00', 'America/Los_Angeles') |
| curtime | curtime() -> time | SELECT curtime() |
| curdate | curdate() -> date | SELECT curdate() FROM my-index LIMIT 1 |
| current_date | current_date() -> date | SELECT current_date() FROM my-index LIMIT 1 |
| current_time | current_time() -> time | SELECT current_time() |
| current_timestamp | current_timestamp() -> date | SELECT current_timestamp() FROM my-index LIMIT 1 |
| date | date(date) -> date | SELECT date() FROM my-index LIMIT 1 |
| datediff | datediff(date, date) -> integer | SELECT datediff(date('2000-01-02'), date('2000-01-01')) |
| datetime | datetime(string) -> datetime | SELECT datetime('2008-12-25 00:00:00') |
| date_add | date_add(date, INTERVAL integer UNIT) | SELECT date_add('2020-08-26'), INTERVAL 1 HOUR) |
| 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 |
| day | day(date) -> integer | SELECT day(date('2020-08-25')) |
| dayname | dayname(date) -> string | SELECT dayname(date('2020-08-26')) FROM my-index LIMIT 1 |
| dayofmonth | dayofmonth(date) -> integer | SELECT dayofmonth(date) FROM my-index LIMIT 1 |
| dayofweek | dayofweek(date) -> integer | SELECT dayofweek(date) 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 |
| day_of_month* | day_of_month(date) -> integer | SELECT day_of_month(date) FROM my-index LIMIT 1 |
| day_of_week* | day_of_week(date) -> integer | SELECT day_of_week(date('2020-08-26')) FROM my-index LIMIT 1 |
| day_of_year* | day_of_year(date) -> integer | SELECT day_of_year(date('2020-08-26')) FROM my-index LIMIT 1 |
| extract* | extract(part FROM date) -> integer | SELECT extract(MONTH FROM datetime('2020-08-26 10:11:12')) |
| from_days | from_days(N) -> integer | SELECT from_days(733687) FROM my-index LIMIT 1 |
| from_unixtime | from_unixtime(N) -> date | SELECT from_unixtime(1220249547) |
| get_format | get_format(PART, string) -> string | SELECT get_format(DATE, 'USA') |
| hour | hour(time) -> integer | SELECT hour((time '01:02:03')) FROM my-index LIMIT 1 |
| hour_of_day* | hour_of_day(time) -> integer | SELECT hour_of_day((time '01:02:03')) FROM my-index LIMIT 1 |
| last_day* | last_day(date) -> integer | SELECT last_day(date('2020-08-26')) |
| localtime | localtime() -> date | SELECT localtime() FROM my-index LIMIT 1 |
| localtimestamp | localtimestamp() -> date | SELECT localtimestamp() FROM my-index LIMIT 1 |
| makedate | makedate(double, double) -> date | SELECT makedate(1945, 5.9) |
| 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 |
| minute_of_day* | minute_of_day(expr) -> integer | SELECT minute_of_day((time '01:02:03')) FROM my-index LIMIT 1 |
| minute_of_hour* | minute_of_hour(expr) -> integer | SELECT minute_of_hour((time '01:02:03')) FROM my-index LIMIT 1 |
| month | month(date) -> integer | SELECT month(date) FROM my-index |
| month_of_year* | month_of_year(date) -> integer | SELECT month_of_year(date) FROM my-index |
| monthname | monthname(date) -> string | SELECT monthname(date) FROM my-index |
| now | now() -> date | SELECT now() FROM my-index LIMIT 1 |
| period_add | period_add(integer, integer) | SELECT period_add(200801, 2) |
| period_diff | period_diff(integer, integer) | SELECT period_diff(200802, 200703) |
| 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 |
| second_of_minute* | second_of_minute(time) -> integer | SELECT second_of_minute((time '01:02:03')) FROM my-index LIMIT 1 |
| sec_to_time* | sec_to_time(integer) -> date | SELECT sec_to_time(10000) |
| subdate | subdate(date, INTERVAL expr unit) -> date, datetime | SELECT subdate(date('2008-01-02'), INTERVAL 31 day) FROM my-index LIMIT 1 |
| subtime | subtime(date, date) -> date | SELECT subtime(date('2008-12-12'), date('2008-11-15')) |
| str_to_date* | str_to_date(string, format) -> date | SELECT str_to_date("March 10 2000", %M %d %Y") |
| time | time(expr) -> time | SELECT time('13:49:00') FROM my-index LIMIT 1 |
| timediff | timediff(time, time) -> time | SELECT timediff(time('23:59:59'), time('13:00:00')) |
| timestamp | timestamp(date) -> date | SELECT timestamp(date) FROM my-index LIMIT 1 |
| timestampadd | timestampadd(interval, integer, date) -> date) | SELECT timestampadd(DAY, 17, datetime('2000-01-01 00:00:00')) |
| timestampdiff | timestampdiff(interval, date, date) -> integer | SELECT timestampdiff(YEAR, '1997-01-01 00:00:00, '2001-03-06 00:00:00') |
| time_format | time_format(date, string) -> string | SELECT time_format('1998-01-31 13:14:15.012345', '%f %H %h %I %i %p %r %S %s %T') |
| time_to_sec | time_to_sec(time) -> long | SELECT time_to_sec(time '22:23:00') FROM my-index LIMIT 1 |
| to_days | to_days(date) -> long | SELECT to_days(date '2008-10-07') FROM my-index LIMIT 1 |
| to_seconds | to_seconds(date) -> integer | SELECT to_seconds(date('2008-10-07') |
| unix_timestamp | unix_timestamp(date) -> double | SELECT unix_timestamp(timestamp('1996-11-15 17:05:42')) |
| utc_date | utc_date() -> date | SELECT utc_date() |
| utc_time | utc_time() -> date | SELECT utc_time() |
| utc_timestamp | utc_timestamp() -> date | SELECT utc_timestamp() |
| week | week(date[mode]) -> integer | SELECT week(date('2008-02-20')) FROM my-index LIMIT 1 |
| weekofyear | weekofyear(date[mode]) -> integer | SELECT weekofyear(date('2008-02-20')) FROM my-index LIMIT 1 |
| week_of_year* | week_of_year(date[mode]) -> integer | SELECT week_of_year(date('2008-02-20')) FROM my-index LIMIT 1 |
| year | year(date) -> integer | SELECT year(date) FROM my-index LIMIT 1 |
| yearweek* | yearweek(date[mode]) -> integer | SELECT yearweek(date('2008-02-20')) 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.