Edit

System functions (Azure Cosmos DB)

  • Article

APPLIES TO: NoSQL

Azure Cosmos DB provides many built-in SQL functions. The categories of built-in functions are listed below.

Function group Description Operations
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, GetCurrentTicks
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, REGEXMATCHREPLACE, 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 Azure Cosmos DB functions and ANSI SQL functions is that Azure 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 undefined, the item is skipped instead of returning an error.

Next steps