System functions (Azure Cosmos DB)
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
Feedback
Submit and view feedback for