Scalar expressions in Azure Cosmos DB SQL queries

Article
10/12/2022
2 minutes to read

APPLIES TO: NoSQL

The SELECT clause supports scalar expressions. A scalar expression is a combination of symbols and operators that can be evaluated to obtain a single value. Examples of scalar expressions include: constants, property references, array element references, alias references, or function calls. Scalar expressions can be combined into complex expressions using operators.

Syntax

<scalar_expression> ::=  
       <constant>
     | input_alias
     | parameter_name  
     | <scalar_expression>.property_name  
     | <scalar_expression>'['"property_name"|array_index']'  
     | unary_operator <scalar_expression>  
     | <scalar_expression> binary_operator <scalar_expression>    
     | <scalar_expression> ? <scalar_expression> : <scalar_expression>  
     | <scalar_function_expression>  
     | <create_object_expression>
     | <create_array_expression>  
     | (<scalar_expression>)
  
<scalar_function_expression> ::=  
        'udf.' Udf_scalar_function([<scalar_expression>][,…n])  
        | builtin_scalar_function([<scalar_expression>][,…n])  
  
<create_object_expression> ::=  
   '{' [{property_name | "property_name"} : <scalar_expression>][,…n] '}'  
  
<create_array_expression> ::=  
   '[' [<scalar_expression>][,…n] ']'

Arguments

<constant>

Represents a constant value. See Constants section for details.
input_alias

Represents a value defined by the input_alias introduced in the FROM clause.
This value is guaranteed to not be undefined –undefined values in the input are skipped.
<scalar_expression>.property_name

Represents a value of the property of an object. If the property doesn't exist or property is referenced on a value, which isn't an object, then the expression evaluates to undefined value.
<scalar_expression>'['"property_name"|array_index']'

Represents a value of the property with name property_name or array element with index array_index of an array. If the property/array index doesn't exist or the property/array index is referenced on a value that isn't an object/array, then the expression evaluates to undefined value.
unary_operator <scalar_expression>

Represents an operator that is applied to a single value.
<scalar_expression> binary_operator <scalar_expression>

Represents an operator that is applied to two values.
<scalar_function_expression>

Represents a value defined by a result of a function call.
udf_scalar_function

Name of the user-defined scalar function.
builtin_scalar_function

Name of the built-in scalar function.
<create_object_expression>

Represents a value obtained by creating a new object with specified properties and their values.
<create_array_expression>

Represents a value obtained by creating a new array with specified values as elements
parameter_name

Represents a value of the specified parameter name. Parameter names must have a single @ as the first character.

Remarks

When calling a built-in or user-defined scalar function, all arguments must be defined. If any of the arguments is undefined, the function won't be called, and the result will be undefined.

When creating an object, any property that is assigned undefined value will be skipped and not included in the created object.

When creating an array, any element value that is assigned undefined value will be skipped and not included in the created object. This will cause the next defined element to take its place in such a way that the created array won't have skipped indexes.

Examples

    SELECT ((2 + 11 % 7)-2)/3

The results are:

    [{
      "$1": 1.33333
    }]

In the following query, the result of the scalar expression is a Boolean:

    SELECT f.address.city = f.address.state AS AreFromSameCityState
    FROM Families f