SQL API Built-In Functions
Cosmos DB also supports a number of built-in functions for common operations, that can be used inside queries. Here are some example of how can be used as part of select columns or the WHERE clause:
Use Built-in functions as part of SELECT columns
SELECT IS_NUMBER(user_id) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM [users] SELECT POWER(user_id, 2) AS POWERSSS, LENGTH(id) AS LENGTH_ID, PI() AS JustThePI FROM [users]
Use Built-in functions as part of WHERE clause
SELECT * FROM [users] WHERE STARTSWITH(middle_name, 'G') SELECT * FROM [users] WHERE REPLACE(middle_name, 'Chr', '___') = '___istopher'
| Function group | Operations |
| Mathematical functions | ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN |
| Type checking functions | IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE |
| String functions | ARRAY, CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER |
| Array functions | ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE |
Mathematical functions
The mathematical functions each perform a calculation, based on input values that are provided as arguments, and return a numeric value. Here's a table of supported built-in mathematical functions.
| Usage | Description |
| ABS (num_expr) | Returns the absolute (positive) value of the specified numeric expression. |
| CEILING (num_expr) | Returns the smallest integer value greater than, or equal to, the specified numeric expression. |
| FLOOR (num_expr) | Returns the largest integer less than or equal to the specified numeric expression. |
| EXP (num_expr) | Returns the exponent of the specified numeric expression. |
| LOG (num_expr [,base]) | Returns the natural logarithm of the specified numeric expression, or the logarithm using the specified base |
| LOG10 (num_expr) | Returns the base-10 logarithmic value of the specified numeric expression. |
| ROUND (num_expr) | Returns a numeric value, rounded to the closest integer value. |
| TRUNC (num_expr) | Returns a numeric value, truncated to the closest integer value. |
| SQRT (num_expr) | Returns the square root of the specified numeric expression. |
| SQUARE (num_expr) | Returns the square of the specified numeric expression. |
| POWER (num_expr, num_expr) | Returns the power of the specified numeric expression to the value specified. |
| SIGN (num_expr) | Returns the sign value (-1, 0, 1) of the specified numeric expression. |
| ACOS (num_expr) | Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine. |
| ASIN (num_expr) | Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine. |
| ATAN (num_expr) | Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent. |
| ATN2 (num_expr) | Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. |
| COS (num_expr) | Returns the trigonometric cosine of the specified angle, in radians, in the specified expression. |
| COT (num_expr) | Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression. |
| DEGREES (num_expr) | Returns the corresponding angle in degrees for an angle specified in radians. |
| PI () | Returns the constant value of PI. |
| RADIANS (num_expr) | Returns radians when a numeric expression, in degrees, is entered. |
| SIN (num_expr) | Returns the trigonometric sine of the specified angle, in radians, in the specified expression. |
| TAN (num_expr) | Returns the tangent of the input expression, in the specified expression. |
Type checking functions
The type checking functions allow you to check the type of an expression within SQL queries. Type checking functions can be used to determine the type of properties within documents dynamically when it is variable or unknown. Here's a table of supported built-in type checking functions.
| Usage | Description |
| IS_ARRAY (expr) | Returns a Boolean indicating if the type of the value is an array. |
| IS_BOOL (expr) | Returns a Boolean indicating if the type of the value is a Boolean. |
| IS_NULL (expr) | Returns a Boolean indicating if the type of the value is null. |
| IS_NUMBER (expr) | Returns a Boolean indicating if the type of the value is a number. |
| IS_OBJECT (expr) | Returns a Boolean indicating if the type of the value is a JSON object. |
| IS_STRING (expr) | Returns a Boolean indicating if the type of the value is a string. |
| IS_DEFINED (expr) | Returns a Boolean indicating if the property has been assigned a value. |
| IS_PRIMITIVE (expr) | Returns a Boolean indicating if the type of the value is a string, number, Boolean or null. |
String functions
The following scalar functions perform an operation on a string input value and return a string, numeric or Boolean value. Here's a table of built-in string functions:
| Usage | Description |
| ARRAY (str_expr) | Project the results of the specified query as an array. |
| LENGTH (str_expr) | Returns the number of characters of the specified string expression |
| CONCAT (str_expr, str_expr [, str_expr]) | Returns a string that is the result of concatenating two or more string values. |
| SUBSTRING (str_expr, num_expr, num_expr) | Returns part of a string expression. |
| STARTSWITH (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression starts with the second |
| ENDSWITH (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression ends with the second |
| CONTAINS (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression contains the second. |
| INDEX_OF (str_expr, str_expr) | Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found. |
| LEFT (str_expr, num_expr) | Returns the left part of a string with the specified number of characters. |
| RIGHT (str_expr, num_expr) | Returns the right part of a string with the specified number of characters. |
| LTRIM (str_expr) | Returns a string expression after it removes leading blanks. |
| RTRIM (str_expr) | Returns a string expression after truncating all trailing blanks. |
| LOWER (str_expr) | Returns a string expression after converting uppercase character data to lowercase. |
| UPPER (str_expr) | Returns a string expression after converting lowercase character data to uppercase. |
| REPLACE (str_expr, str_expr, str_expr) | Replaces all occurrences of a specified string value with another string value. |
| REPLICATE (str_expr, num_expr) | Repeats a string value a specified number of times. |
| REVERSE (str_expr) | Returns the reverse order of a string value. |
Array functions
The following scalar functions perform an operation on an array input value and return numeric, Boolean or array value. Here's a table of built-in array functions:
| Usage | Description |
| ARRAY_LENGTH (arr_expr) | Returns the number of elements of the specified array expression. |
| ARRAY_CONCAT (arr_expr, arr_expr [, arr_expr]) | Returns an array that is the result of concatenating two or more array values. |
| ARRAY_CONTAINS (arr_expr, expr [, bool_expr]) | Returns a Boolean indicating whether the array contains the specified value. Can specify if the match is full or partial. |
| ARRAY_SLICE (arr_expr, num_expr [, num_expr]) | Returns part of an array expression. |
Nested functions
You can also perform nested built-in functions, wich will be processed server side as well:
i.e. SELECT TOP 10 CONCAT(SUBSTRING(UPPER(cuisine), 0, 3), '-cuisine') FROM [restaurants]