Functions and Operators

Elasticsearch SQL provides a comprehensive set of built-in operators and functions:

• Operators

  • Equality (=)
  • Null safe Equality (<=>)
  • Inequality (<> or !=)
  • Comparison (<, <=, >, >=)
  • BETWEEN
  • IS NULL/IS NOT NULL
  • IN (<value1>, <value2>, ...)
  • AND
  • OR
  • NOT
  • Add (+)
  • Subtract (infix -)
  • Negate (unary -)
  • Multiply (*)
  • Divide (/)
  • Modulo or Remainder(%)
  • Cast (::)

• LIKE and RLIKE Operators

  • LIKE
  • RLIKE

• Aggregate Functions

  • AVG
  • COUNT
  • COUNT(ALL)
  • COUNT(DISTINCT)
  • FIRST/FIRST_VALUE
  • LAST/LAST_VALUE
  • MAX
  • MIN
  • SUM
  • KURTOSIS
  • MAD
  • PERCENTILE
  • PERCENTILE_RANK
  • SKEWNESS
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM_OF_SQUARES
  • VAR_POP
  • VAR_SAMP

• Grouping Functions

  • HISTOGRAM

• Date-Time Operators

• Date-Time Functions

  • CURRENT_DATE/CURDATE
  • CURRENT_TIME/CURTIME
  • CURRENT_TIMESTAMP
  • DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD
  • DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF
  • DATE_FORMAT
  • DATE_PARSE
  • DATETIME_FORMAT
  • DATETIME_PARSE
  • FORMAT
  • DATE_PART/DATEPART
  • DATE_TRUNC/DATETRUNC
  • DAY_OF_MONTH/DOM/DAY
  • DAY_OF_WEEK/DAYOFWEEK/DOW
  • DAY_OF_YEAR/DOY
  • DAY_NAME/DAYNAME
  • EXTRACT
  • HOUR_OF_DAY/HOUR
  • ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW
  • ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW
  • MINUTE_OF_DAY
  • MINUTE_OF_HOUR/MINUTE
  • MONTH_OF_YEAR/MONTH
  • MONTH_NAME/MONTHNAME
  • NOW
  • SECOND_OF_MINUTE/SECOND
  • QUARTER
  • TIME_PARSE
  • TO_CHAR
  • TODAY
  • WEEK_OF_YEAR/WEEK
  • YEAR

• Full-Text Search Functions

  • MATCH
  • QUERY
  • SCORE

• Mathematical Functions

  • ABS
  • CBRT
  • CEIL/CEILING
  • E
  • EXP
  • EXPM1
  • FLOOR
  • LOG
  • LOG10
  • PI
  • POWER
  • RANDOM/RAND
  • ROUND
  • SIGN/SIGNUM
  • SQRT
  • TRUNCATE/TRUNC
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • COS
  • COSH
  • COT
  • DEGREES
  • RADIANS
  • SIN
  • SINH
  • TAN

• String Functions

  • ASCII
  • BIT_LENGTH
  • CHAR
  • CHAR_LENGTH
  • CONCAT
  • INSERT
  • LCASE
  • LEFT
  • LENGTH
  • LOCATE
  • LTRIM
  • OCTET_LENGTH
  • POSITION
  • REPEAT
  • REPLACE
  • RIGHT
  • RTRIM
  • SPACE
  • SUBSTRING
  • TRIM
  • UCASE

• Type Conversion Functions

  • CAST
  • CONVERT

• Conditional Functions And Expressions

  • CASE
  • COALESCE
  • GREATEST
  • IFNULL
  • IIF
  • ISNULL
  • LEAST
  • NULLIF
  • NVL

• Geo Functions

  • ST_AsWKT
  • ST_Distance
  • ST_GeometryType
  • ST_WKTToSQL
  • ST_X
  • ST_Y
  • ST_Z

• System Functions

  • DATABASE
  • USER