System functions (Azure Cosmos DB)
Cosmos DB provides many built-in SQL functions. The categories of built-in functions are listed below.
|Array functions||The array functions perform an operation on an array input value and return numeric, Boolean, or array value.||ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, ARRAY_SLICE|
|Date and Time functions||The date and time functions allow you to get the current UTC date and time in two forms; a numeric timestamp whose value is the Unix epoch in milliseconds or as a string which conforms to the ISO 8601 format.||GetCurrentDateTime, GetCurrentTimestamp|
|Mathematical functions||The mathematical functions each perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.||ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, TAN, TRUNC|
|Spatial functions||The spatial functions perform an operation on a spatial object input value and return a numeric or Boolean value.||ST_DISTANCE, ST_INTERSECTS, ST_ISVALID, ST_ISVALIDDETAILED, ST_WITHIN|
|String functions||The string functions perform an operation on a string input value and return a string, numeric or Boolean value.||CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, StringToArray, StringToBoolean, StringToNull, StringToNumber, StringToObject, SUBSTRING, ToString, TRIM, UPPER|
|Type checking functions||The type checking functions allow you to check the type of an expression within SQL queries.||IS_ARRAY, IS_BOOL, IS_DEFINED, IS_NULL, IS_NUMBER, IS_OBJECT, IS_PRIMITIVE, IS_STRING|
Built-in versus User Defined Functions (UDFs)
If you’re currently using a user-defined function (UDF) for which a built-in function is now available, the corresponding built-in function will be quicker to run and more efficient.
Built-in versus ANSI SQL functions
The main difference between Cosmos DB functions and ANSI SQL functions is that Cosmos DB functions are designed to work well with schemaless and mixed-schema data. For example, if a property is missing or has a non-numeric value like
unknown, the item is skipped instead of returning an error.