You're viewing version 3.4 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

PPL supports a wide range of built-in functions for data processing and analysis. These functions are organized into categories based on their functionality and can be used within PPL queries to manipulate and transform data.

• Aggregation functions:
  • COUNT.
  • SUM.
  • AVG.
  • MAX.
  • MIN.
  • VAR_SAMP.
  • VAR_POP.
  • STDDEV_SAMP.
  • STDDEV_POP.
  • DISTINCT_COUNT, DC.
  • DISTINCT_COUNT_APPROX.
  • EARLIEST.
  • LATEST.
  • TAKE.
  • PERCENTILE, PERCENTILE_APPROX.
  • MEDIAN.
  • FIRST.
  • LAST.
  • LIST.
  • VALUES.
• Collection functions:
  • ARRAY.
  • ARRAY_LENGTH.
  • FORALL.
  • EXISTS.
  • FILTER.
  • TRANSFORM.
  • REDUCE.
  • MVJOIN.
  • MVAPPEND.
  • SPLIT.
  • MVDEDUP.
  • MVFIND.
  • MVINDEX.
  • MVMAP.
  • MVZIP.
• Conditional functions:
  • ISNULL.
  • ISNOTNULL.
  • EXISTS.
  • IFNULL.
  • NULLIF.
  • IF.
  • CASE.
  • COALESCE.
  • ISPRESENT.
  • ISBLANK.
  • ISEMPTY.
  • EARLIEST.
  • LATEST.
  • REGEXP_MATCH.
• Type conversion functions:
  • CAST.
  • TOSTRING.
  • TONUMBER.
• Cryptographic functions:
  • MD5.
  • SHA1.
  • SHA2.
• Date and time functions:
  • ADDDATE.
  • ADDTIME.
  • CONVERT_TZ.
  • CURDATE.
  • CURRENT_DATE.
  • CURRENT_TIME.
  • CURRENT_TIMESTAMP.
  • CURTIME.
  • DATE.
  • DATE_ADD.
  • DATE_FORMAT.
  • DATETIME.
  • DATE_SUB.
  • DATEDIFF.
  • DAY.
  • DAYNAME.
  • DAYOFMONTH.
  • DAY_OF_MONTH.
  • DAYOFWEEK.
  • DAY_OF_WEEK.
  • DAYOFYEAR.
  • DAY_OF_YEAR.
  • EXTRACT.
  • FROM_DAYS.
  • FROM_UNIXTIME.
  • GET_FORMAT.
  • HOUR.
  • HOUR_OF_DAY.
  • LAST_DAY.
  • LOCALTIMESTAMP.
  • LOCALTIME.
  • MAKEDATE.
  • MAKETIME.
  • MICROSECOND.
  • MINUTE.
  • MINUTE_OF_HOUR.
  • MONTH.
  • MONTH_OF_YEAR.
  • MONTHNAME.
  • NOW.
  • PERIOD_ADD.
  • PERIOD_DIFF.
  • QUARTER.
  • SEC_TO_TIME.
  • SECOND.
  • SECOND_OF_MINUTE.
  • STRFTIME.
  • STR_TO_DATE.
  • SUBDATE.
  • SUBTIME.
  • SYSDATE.
  • TIME.
  • TIME_FORMAT.
  • TIME_TO_SEC.
  • TIMEDIFF.
  • TIMESTAMP.
  • TIMESTAMPADD.
  • TIMESTAMPDIFF.
  • TO_DAYS.
  • TO_SECONDS.
  • UNIX_TIMESTAMP.
  • UTC_DATE.
  • UTC_TIME.
  • UTC_TIMESTAMP.
  • WEEK.
  • WEEKDAY.
  • WEEK_OF_YEAR.
  • YEAR.
  • YEARWEEK.
• Expressions:
  • Arithmetic operators.
  • Predicate operators.
• IP address functions:
  • CIDRMATCH.
  • GEOIP.
• JSON functions:
  • JSON.
  • JSON_VALID.
  • JSON_OBJECT.
  • JSON_ARRAY.
  • JSON_ARRAY_LENGTH.
  • JSON_EXTRACT.
  • JSON_DELETE.
  • JSON_SET.
  • JSON_APPEND.
  • JSON_EXTEND.
  • JSON_KEYS.
• Mathematical functions:
  • ADD.
  • SUBTRACT.
  • MULTIPLY.
  • DIVIDE.
  • SUM.
  • AVG.
  • ACOS.
  • ASIN.
  • ATAN.
  • ATAN2.
  • CEIL.
  • CEILING.
  • CONV.
  • COS.
  • COSH.
  • COT.
  • CRC32.
  • DEGREES.
  • E.
  • EXP.
  • EXPM1.
  • FLOOR.
  • LN.
  • LOG.
  • LOG2.
  • LOG10.
  • MOD.
  • MODULUS.
  • PI.
  • POW.
  • POWER.
  • RADIANS.
  • RAND.
  • ROUND.
  • SIGN.
  • SIGNUM.
  • SIN.
  • SINH.
  • SQRT.
  • CBRT.
  • RINT.
• Relevance functions:
  • MATCH.
  • MATCH_PHRASE.
  • MATCH_PHRASE_PREFIX.
  • MULTI_MATCH.
  • SIMPLE_QUERY_STRING.
  • MATCH_BOOL_PREFIX.
  • QUERY_STRING.
• Statistical functions:
  • MAX.
  • MIN.
• String functions:
  • CONCAT.
  • CONCAT_WS.
  • LENGTH.
  • LIKE.
  • ILIKE.
  • LOCATE.
  • LOWER.
  • LTRIM.
  • POSITION.
  • REPLACE.
  • REVERSE.
  • RIGHT.
  • RTRIM.
  • SUBSTRING.
  • TRIM.
  • UPPER.
  • REGEXP_REPLACE.
• System functions:
  • TYPEOF.

Related documentation

• Aggregation functions
• Collection functions
• Conditional functions
• Type conversion functions
• Cryptographic functions
• Date and time functions
• Expressions
• IP address functions
• JSON functions
• Mathematical functions
• Relevance functions
• Statistical functions
• String functions
• System functions