Casts

Use AS keyword to convert selection to the target object type.

tip

Please note, that it is possible to use a keyword as with both aliases and casts. The following principle applies:

1. AS works as alias in case a variable after AS does not match any existing object and such a variable is used to name a column.
2. AS works as cast in case a variable after AS matches an existing object. Embrace a variable name after AS in single quotes to treat it as alias for column naming.

Casting examples

-- select Info objects of a LimitOrderInfo type and convert output objects to LimitOrderInfo object type
SELECT order.info AS deltix.orders.LimitOrderInfo from orders

-- take a polymorphic object Entry and convert it to the fixed type object L1Entry
-- query returns NULL in case conversion is not possible.
SELECT entry AS L1Entry FROM binance
ARRAY JOIN entries AS entry

-- in this case we convert Entry object to a polymorphic object type that includes objects L1Entry and L2EntryNew
-- object keyword in this case allows us to specify a comma-separated list of objects.
SELECT entry AS object(L1Entry, L2EntryNew) FROM binance
ARRAY JOIN entries AS entry

Previous examples used existing object types. For example, L1Entry and L2EntryNew are existing object types in the entry polymorphic object. A cast to any of them would reduce the source object. Nevertheless, you can cast to non-existent object types and extend the source object.

Casting to a new object type

SELECT entry AS object(L1Entry, L2EntryNew, SecurityStatusMessage) FROM binance
ARRAY JOIN entries AS entry

In this example, the query returns NULL for all SecurityStatusMessage because the entry object does not have this object type. Hence, fields with NULL values will have the SecurityStatusMessage object type.

Cast Arrays

It is also possible to use cast with arrays. Use casts to create a fixed type and polymorphic arrays.

Using Casting with arrays

-- takes a polymorphic array Entries and creates a fixed type array with only TradeEntry type of array, returns NULL for other array types
SELECT entries AS array(TradeEntry) FROM binance

-- filter out NULL values
SELECT (entries AS array(TradeEntry))[THIS IS NOT NULL] FROM binance

-- cast to a polymorphic array
SELECT entries AS array(L1Entry, L2EntryNew) FROM binance

-- cast arrays of entries to L1Entry format
SELECT 
entry[side == ASK].price AS askPrice, 
entry[side == ASK].size AS askSize, 
entry[side == BID].price AS bidPrice, 
entry[side == BID].size AS bidSize
FROM binance 
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS entry

-- in this example, we select Prices from Entries array and cast it to TradeEntry object type
SELECT (entries AS array(TradeEntry)).price AS Price FROM binance
WHERE size(entries[THIS IS TradeEntry]) > 0

Cast Primitives

Use cast to convert primitives data types.

tip

Please note, that casting from higher to lower precision types causes data loss. For example cast from Int16 to Int8.

Casting primitive data types

-- Cast a byte field to all possible types
SELECT 
byteField AS INT8, byteField AS INT16, byteField AS INT32, byteField AS INT64, byteField as DECIMAL, byteField AS FLOAT32, byteField AS FLOAT64, 
byteField AS CHAR, byteField AS BOOLEAN, byteField as TIMESTAMP, byteField as TIMESTAMP(NS), byteField as VARCHAR
FROM alltypes

-- Cast an array of primitives
SELECT entries[THIS IS TradeEntry].price AS array(float64) FROM binance

-- Cast Integer <-> timestamp
-- result: 1970-01-01 00:00:01.000, 2000, [1000,2000]
select 1000 as timestamp, '1970-01-01 00:00:02.000'd as int64, ['1970-01-01 00:00:01.000'd, '1970-01-01 00:00:02.000'd] as array(int64)

-- Cast Varchar to Number (parse string to numer)
SELECT '-12345345' as int32, '-333.333' as decimal

-- Cast Varchar to Timestamp (parse string to timestamp)
SELECT '2016-10-27T16:36:08.993' as timestamp(ms), '2016-10-27T16:36:08.123456993' as timestamp(ns)

-- Use toTimestamp (or toTimestampNs) function to parse Timestamp with specified format.
SELECT toTimestamp('2023-08-27 22:32:02.123', 'yyyy-MM-dd HH:mm:ss.SSS')

tip

By default, the TIMESTAMP type has millisecond resolution. However, you can specify a different precision by casting to either TIMESTAMP(MS) for milliseconds or TIMESTAMP(NS) for nanoseconds.

info

Timestamp(ms) (alias to Timestamp, has millisecond resolution) and Timestamp(ns) (nanosecond resolution) types are available since 5.6.67+.

info

Parsing Varchar string into Number or Timestamp is available since 5.6.111+.

From\To	INT8	INT16	INT32	INT64	FLOAT32	FLOAT64	DECIMAL	BOOLEAN	CHAR	TIMESTAMP	TIMESTAMP(NS)	VARCHAR
INT8	+	+	+	+	+	+	+	+	+	+	+	+
INT16	+	+	+	+	+	+	+	+	+	+	+	+
INT32	+	+	+	+	+	+	+	+	+	+	+	+
INT64	+	+	+	+	+	+	+	+	+	+	+	+
FLOAT32	+	+	+	+	+	+	+	+	+	+	+	+
FLOAT64	+	+	+	+	+	+	+	+	+	+	+	+
DECIMAL	+	+	+	+	+	+	+	+	+	+	+	+
BOOLEAN	+	+	+	+	+	+	+	+	+	+	+	+
CHAR	+	+	+	+	+	+	+	+	+	+	+	+
TIMESTAMP	+	+	+	+	+	+	+	+	+	+	+	+
TIMESTAMP(NS)	+	+	+	+	+	+	+	+	+	+	+	+
VARCHAR	+	+	+	+	+	+	+	+	-	+	+	+
ENUM	-	-	-	-	-	-	-	-	-	-	-	+