NoSQL query language functions

2025-07-03
Applies to: Azure Cosmos DB for NoSQL, Cosmos DB in Fabric

The NoSQL query language includes a series of system functions that are built in to the query language and designed to handle a wide variety of common tasks.

Functions

Here's a list of functions that the NoSQL query language currently supports:

Mathematical functions

	Description
`ABS`	The `ABS` function calculates the absolute (positive) value of the specified numeric expression.
`ACOS`	The `ACOS` function calculates the trigonometric arccosine of the specified numeric value. The arccosine is the angle, in radians, whose cosine is the specified numeric expression.
`ASIN`	The `ASIN` function calculates the trigonometric arcsine of the specified numeric value. The arcsine is the angle, in radians, whose sine is the specified numeric expression.
`ATAN`	The `ATAN` function calculates the trigonometric arctangent of the specified numeric value. The arctangent is the angle, in radians, whose tangent is the specified numeric expression.
`ATN2`	The `ATN2` function calculates the principal value of the arctangent of `y/x`, expressed in radians.
`CEILING`	The `CEILING` function calculates the smallest integer value greater than or equal to the specified numeric expression.
`COS`	The `COS` function calculates the trigonometric cosine of the specified angle in radians.
`COT`	The `COT` function calculates the trigonometric cotangent of the specified angle in radians.
`DEGREES`	The `DEGREES` function calculates the corresponding angle in degrees for an angle specified in radians.
`EXP`	The `EXP` function calculates the exponential value of the specified numeric expression.
`FLOOR`	The `FLOOR` function calculates the largest integer less than or equal to the specified numeric expression.
`INTADD`	The `INTADD` function returns the sum of two integer values.
`INTBITAND`	The `INTBITAND` function returns a comparison of the bits of each operand using an inclusive AND operator.
`INTBITLEFTSHIFT`	The `INTBITLEFTSHIFT` function returns the result of a bitwise left shift operation on an integer value.
`INTBITNOT`	The `INTBITNOT` function returns the result of a bitwise NOT operation on an integer value.
`INTBITOR`	The `INTBITOR` function returns the result of a bitwise inclusive OR operation on two integer values.
`INTBITRIGHTSHIFT`	The `INTBITRIGHTSHIFT` function returns the result of a bitwise right shift operation on an integer value.
`INTBITXOR`	The `INTBITXOR` function returns the result of a bitwise exclusive OR operation on two integer values.
`INTDIV`	The `INTDIV` function returns the result of dividing the first integer value by the second.
`INTMOD`	The `INTMOD` function returns the remainder of dividing the first integer value by the second.
`INTMUL`	The `INTMUL` function returns the product of two integer values.
`INTSUB`	The `INTSUB` function returns the result of subtracting the second integer value from the first.
`LOG`	The `LOG` function returns the natural logarithm of the specified numeric expression.
`LOG10`	The `LOG10` function returns the base-10 logarithm of the specified numeric expression.
`NUMBERBIN`	The `NUMBERBIN` function calculates the input value rounded to a multiple of the specified size.
`PI`	The `PI` function returns the constant value of Pi.
`POWER`	The `POWER` function returns the value of the specified expression multipled by itself the given number of times.
`RADIANS`	The `RADIANS` function returns the corresponding angle in radians for an angle specified in degrees.
`RAND`	The `RAND` function returns a randomly generated numeric value from zero to one.
`ROUND`	The `ROUND` function returns a numeric value rounded to the closest integer value.
`SIGN`	The `SIGN` function returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
`SIN`	The `SIN` function returns the trigonometric sine of the specified angle in radians.
`SQRT`	The `SQRT` function returns the square root of the specified numeric value.
`SQUARE`	The `SQUARE` function returns the square of the specified numeric value.
`TAN`	The `TAN` function returns the trigonometric tangent of the specified angle in radians.
`TRUNC`	The `TRUNC` function returns a numeric value truncated to the closest integer value.

Array functions

	Description
`ARRAY_CONCAT`	The `ARRAY_CONCAT` function returns an array that is the result of concatenating two or more array values.
`ARRAY_CONTAINS_ALL`	The `ARRAY_CONTAINS_ALL` function returns a boolean indicating whether the array contains all of the specified values.
`ARRAY_CONTAINS_ANY`	The `ARRAY_CONTAINS_ANY` function returns a boolean indicating whether the array contains any of the specified values.
`ARRAY_CONTAINS`	The `ARRAY_CONTAINS` function returns a boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the function.
`ARRAY_LENGTH`	The `ARRAY_LENGTH` function returns the number of elements in the specified array expression.
`ARRAY_SLICE`	The `ARRAY_SLICE` function returns a subset of an array expression using the index and length specified.
`CHOOSE`	The `CHOOSE` function returns the expression at the specified index of a list, or Undefined if the index exceeds the bounds of the list.
`OBJECTTOARRAY`	The `OBJECTTOARRAY` function converts field/value pairs in a JSON object to a JSON array.
`SETINTERSECT`	The `SETINTERSECT` function returns the set of expressions that is contained in both input arrays with no duplicates.
`SETUNION`	The `SETUNION` function returns a set of expressions containing all expressions from two gathered sets with no duplicates.

Aggregation functions

	Description
`AVG`	The `AVG` function calculates the average of the values in the expression.
`COUNT`	The `COUNT` function returns the count of the values in the expression.
`MAX`	The `MAX` function returns the maximum value of the specified expression.
`MIN`	The `MIN` function returns the minimum value of the specified expression.
`SUM`	The `SUM` function calculates the sum of the values in the expression.

String functions

	Description
`CONCAT`	The `CONCAT` function returns a string that is the result of concatenating multiple fields from a document.
`CONTAINS`	The `CONTAINS` function returns a boolean indicating whether the first string expression contains the second string expression.
`ENDSWITH`	The `ENDSWITH` function returns a boolean indicating whether a string ends with the specified suffix. Optionally, the comparison can be case-insensitive.
`INDEX-OF`	The `INDEX_OF` function returns the index of the first occurrence of a string.
`LEFT`	The `LEFT` function returns the left part of a string up to the specified number of characters.
`LENGTH`	The `LENGTH` function returns the number of characters in the specified string expression.
`LOWER`	The `LOWER` function returns a string expression after converting uppercase character data to lowercase.
`LTRIM`	The `LTRIM` function returns a string expression after it removes leading whitespace or specified characters.
`REGEXMATCH`	The `REGEXMATCH` function returns a boolean indicating whether the provided string matches the specified regular expression. Regular expressions are a concise and flexible notation for finding patterns of text.
`REPLACE`	The `REPLACE` function returns a string with all occurrences of a specified string replaced.
`REPLICATE`	The `REPLICATE` function returns a string value repeated a specific number of times.
`REVERSE`	The `REVERSE` function returns the reverse order of a string value.
`RIGHT`	The `RIGHT` function returns the right part of a string up to the specified number of characters.
`RTRIM`	The `RTRIM` function returns a string expression after it removes trailing whitespace or specified characters.
`STARTSWITH`	The `STARTSWITH` function returns a boolean value indicating whether the first string expression starts with the second.
`STRINGEQUALS`	The `STRINGEQUALS` function returns a boolean indicating whether the first string expression matches the second.
`STRINGJOIN`	The `STRINGJOIN` function returns a string, which concatenates the elements of a specified array, using the specified separator between each element.
`STRINGSPLIT`	The `STRINGSPLIT` function returns an array of substrings obtained from separating the source string by the specified delimiter.
`STRINGTOARRAY`	The `STRINGTOARRAY` function converts a string expression to an array.
`STRINGTOBOOLEAN`	The `STRINGTOBOOLEAN` function converts a string expression to a boolean.
`SUBSTRING`	The `SUBSTRING` function returns part of a string expression starting at the specified position and of the specified length, or to the end of the string.
`TOSTRING`	The `TOSTRING` function returns a string representation of a value.
`TRIM`	The `TRIM` function returns a string expression after it removes leading and trailing whitespace or custom characters.
`UPPER`	The `UPPER` function returns a string expression after converting lowercase character data to uppercase.

Date and time functions

	Description
`DATETIMEADD`	The `DATETIMEADD` function returns a date and time string value that is the result of adding a specified number value to the provided date and time string.
`DATETIMEBIN`	The `DATETIMEBIN` function returns a date and time string value that is the result of binning (or rounding) a part of the provided date and time string.
`DATETIMEDIFF`	The `DATETIMEDIFF` function returns the difference, as a signed integer, of the specified date and time part between two date and time values.
`DATETIMEFROMPARTS`	The `DATETIMEFROMPARTS` function returns a date and time string value constructed from input numeric values for various date and time parts.
`DATETIMEPART`	The `DATETIMEPART` function returns the value of the specified date and time part for the provided date and time.
`DATETIMETOTICKS`	The `DATETIMETOTICKS` function converts the specified DateTime to ticks. A single tick represents 100 nanoseconds or 0.0000001 of a second.
`DATETIMETOTIMESTAMP`	The `DATETIMETOTIMESTAMP` function converts the specified date and time to a numeric timestamp. The timestamp is a signed numeric integer that measures the milliseconds since the Unix epoch.
`GETCURRENTDATETIME`	The `GETCURRENTDATETIME` function returns the current UTC (Coordinated Universal Time) date and time as an ISO 8601 string.
`GETCURRENTDATETIMESTATIC`	The `GETCURRENTDATETIMESTATIC` function returns the same UTC date and time value for all items in the query, as an ISO 8601 string. This is useful for consistent timestamps across query results.
`GETCURRENTTICKS`	The `GETCURRENTTICKS` function returns the current UTC time as the number of 100-nanosecond intervals (ticks) that have elapsed since 0001-01-01T00:00:00.0000000Z.
`GETCURRENTTICKSSTATIC`	The `GETCURRENTTICKSSTATIC` function returns a static nanosecond ticks value (100-nanosecond intervals since the Unix epoch) for all items in the same partition.
`GETCURRENTTIMESTAMP`	The `GETCURRENTTIMESTAMP` function returns the current timestamp in milliseconds since the Unix epoch.
`GETCURRENTTIMESTAMPSTATIC`	The `GETCURRENTTIMESTAMPSTATIC` function returns a static timestamp value (milliseconds since the Unix epoch) for all items in the same partition.
`TICKSTODATETIME`	The `TICKSTODATETIME` function converts the specified number of ticks to a date and time value.
`TIMESTAMPTODATETIME`	The `TIMESTAMPTODATETIME` function converts the specified timestamp to a date and time value.

Item functions

	Description
`DOCUMENTID`	The `DOCUMENTID` function returns the unique document ID for a given item in the container.

Full text search functions

	Description
`FULLTEXTCONTAINS`	The `FULLTEXTCONTAINS` function returns a boolean indicating whether the keyword string expression is contained in a specified property path.
`FULLTEXTCONTAINSALL`	The `FULLTEXTCONTAINSALL` function returns a boolean indicating whether all of the provided string expressions are contained in a specified property path.
`FULLTEXTCONTAINSANY`	The `FULLTEXTCONTAINSANY` function returns a boolean indicating whether any of the provided string expressions are contained in a specified property path.
`FULLTEXTSCORE`	The `FULLTEXTSCORE` function returns a BM25 score value that can only be used in an `ORDER BY RANK` clause to sort results from highest relevancy to lowest relevancy of the specified terms.
`RRF`	The `RRF` function returns a fused score by combining two or more scores provided by other functions.

Conditional functions

	Description
`IIF`	The `IIF` function returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Type checking functions

	Description
`IS_ARRAY`	The `IS_ARRAY` function returns a boolean value indicating if the type of the specified expression is an array.
`IS_BOOL`	The `IS_BOOL` function returns a boolean value indicating if the type of the specified expression is a boolean.
`IS_DEFINED`	The `IS_DEFINED` function returns a boolean indicating if the property has been assigned a value.
`IS_FINITE_NUMBER`	The `IS_FINITE_NUMBER` function returns a boolean indicating if a number is a finite number (not infinite).
`IS_INTEGER`	The `IS_INTEGER` function returns a boolean indicating if a number is a 64-bit signed integer. 64-bit signed integers range from `-9,223,372,036,854,775,808` to `9,223,372,036,854,775,807`. For more information, see __int64.
`IS_NULL`	The `IS_NULL` function returns a boolean value indicating if the type of the specified expression is `null`.
`IS_NUMBER`	The `IS_NUMBER` function returns a boolean value indicating if the type of the specified expression is a number.
`IS_OBJECT`	The `IS_OBJECT` function returns a boolean value indicating if the type of the specified expression is a JSON object.
`IS_PRIMITIVE`	The `IS_PRIMITIVE` function returns a boolean value indicating if the type of the specified expression is a primitive (string, boolean, numeric, or null).
`IS_STRING`	The `IS_STRING` function returns a boolean value indicating if the type of the specified expression is a string.
`STRINGTONULL`	The `STRINGTONULL` function converts a string expression to `null`.
`STRINGTONUMBER`	The `STRINGTONUMBER` function converts a string expression to a number.
`STRINGTOOBJECT`	The `STRINGTOOBJECT` function converts a string expression to an object.

Spatial functions

	Description
`ST_AREA`	The `ST_AREA` function returns the total area of a GeoJSON Polygon or MultiPolygon expression.
`ST_DISTANCE`	The `ST_DISTANCE` function returns the distance between two GeoJSON Point, Polygon, MultiPolygon or LineString expressions.
`ST_INTERSECTS`	The `ST_INTERSECTS` function returns a boolean indicating whether the GeoJSON object specified in the first argument intersects the GeoJSON object in the second argument.
`ST_ISVALID`	The `ST_ISVALID` function returns a boolean value indicating whether the specified GeoJSON Point, Polygon, MultiPolygon, or LineString expression is valid.
`ST_ISVALIDDETAILED`	The `ST_ISVALIDDETAILED` function returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, the reason.
`ST_WITHIN`	The `ST_WITHIN` function returns a boolean expression indicating whether the GeoJSON object specified in the first argument is within the GeoJSON object in the second argument.
`VECTORDISTANCE`	The `VECTORDISTANCE` function returns the similarity score between two specified vectors.