1. SQL Reference Guide
  2. SQL Query Management
  3. Primary Expressions
  4. Cast Expression

Cast Expression

Syntax

cast_expression ::= CAST "(" expression AS sequence_type ")"

Semantics

The cast expression creates, if possible, new items of a given target type from the items of its input sequence. Specifically, a cast expression is evaluated as follows:

A cardinality check is performed first:
  1. if the quantifier of the target type is * the sequence may have any number of items,
  2. if the quantifier is + the input sequence must have at least one item,
  3. if the quantifier is ? the input sequence must have at most one item, and
  4. if there is no quantifier, the input sequence must have exactly one item.
If the cardinality of the input sequence does not match the quantifier of the target type, an error is raised. Then, each input item is cast to the target item type according to the following (recursive) rules.
  • If the type of the input item is equal to the target item type, the cast is a no-op: the input item itself is returned.
  • If the target type is a wildcard type other than JSON and the type of the input item is a subtype of the wildcard type, the cast is a no-op; Otherwise an error is raised.
  • If the target type is JSON, then (a) an error is raised if the input item is has a non-json atomic type, else (b) if the input item has a type that is a json atomic type or ARRAY(JSON) or MAP(JSON), the cast is a no-op , else (c) if the input item is a non-json array, a new array of type ARRAY(JSON) is constructed, each element of the input array is cast to JSON, and the resulting item is appended into the new json array, else (d) if the input item is a non-json map, a new map of type MAP(JSON) is constructed, each field value of the input map is cast to JSON, and resulting item together with the associated field name are inserted into the new json map, else (e) if the input item is a record, it is cast to a map of type MAP(JSON) as described below.
  • If the target type is an array type, an error is raised if the input item is not an array. Otherwise, a new array is created, whose type is the target type, each element in the input array is cast to the element type of the target array, and the resulting item is appended into the new array.
  • If the target type is a map type, an error is raised if the input item is not a map or a record. Otherwise, a new map is created, whose type is the target type, each field value in the input map/record is cast to the value type of the target map, and the resulting field value together with the associated field name are inserted to the new map.
  • If the target type is a record type, an error is raised if the input item is not a record or a map. Otherwise, a new record is created, whose type is the target type. If the input item is a record, its type must have the same fields and in the same order as the target type. In this case, each field value in the input record is cast to the value type of the corresponding field in the target type and the resulting field value together with the associated field name are added to the new record. If the input item is a map, then for each map field, if the field name exists in the target type, the associated field value is cast to the value type of the corresponding field in the target type and the resulting field value together with the associated field name are added to the new record. Any fields in the new record whose names do not appear in the input map have their associated field values set to their default values.
  • If the target type is string, the input item may be of any type. In other words, every item can be cast to a string. For complex items their “string value” is a json-text representation of their value. For timestamps, their string value is in UTC and has the format "uuuu-MM-dd['T'HH:mm:ss]". For binary items, their string value is a base64 encoding of their bytes.
  • If the target type is an atomic type other than string, the input item must also be atomic. Among atomic items and types the following casts are allowed:
    • Every numeric item can be cast to every other numeric type. The cast is done as in Java.
    • Integers and longs can be cast to timestamps. The input value is interpreted as the number of milliseconds since January 1, 1970, 00:00:00 GMT.
    • String items may be castable to all other atomic types. Whether the cast succeeds or not depends on whether the actual string value can be parsed into a value that belongs to the domain of the target type.
    • Timestamp items are castable to all the timestamp types. If the target type has a smaller precision that the input item, the resulting timestamp is the one closest to the input timestamp in the target precision. For example, consider the following 2 timestamps with precision 3: 2016-11-01T10:00:00.236 and 2016-11-01T10:00:00.267. The result of casting these timestamps to precision 1 is: 2016-11-01T10:00:00.2 and 2016-11-01T10:00:00.3, respectively.

Example 6-56 Cast Expression

Select the last name of users who moved to their current address in 2015 or later.

SELECT u.lastName FROM Users u
WHERE 
CAST (u.address.startDate AS Timestamp(0)) >=
CAST ("2015-01-01T00:00:00" AS Timestamp(0));

Since there is no literal for Timestamp values, to create such a value a string has to cast to a Timestamp type.