Array Type
Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
An array is a sequential collection of elements in a single SQL value. Arrays can model dependent information with an inherent ordering in a single row. For example, time series data from longitudinal studies or embedding vectors from machine learning models.
Array type syntax:
The optional not null specifier indicates if array elements can be null. For example, array(integer not null) represents a range of non-null integer elements.
All array types have these properties.
- Arrays have variable length. Within a column, arrays aren’t required to have the same number of elements.
- Arrays are one-dimensional.
For a comprehensive overview of supported operations on arrays, see Array Functions.
You can create arrays in two ways.
-
By using the Array Constructor syntax.
-
By using a cast from string data.
Arrays are strongly typed and use the array’s element type for all elements in the array. The element type is a defining part of the array’s overall type, meaning that array(integer) is a different type than array(boolean) or array(text). You can build an array from any atomic type supported by Data 360 SQL.
Part of an array’s element type is the element’s nullability. For example, array(smallint) is different from array(smallint not null).
These four options represent different array types.
| Type | Array nullable? | Elements nullable? | Possible values |
|---|---|---|---|
array(integer) | yes | yes | [],[1,2,3],[1,2,null], null |
array(integer not null) | yes | no | [],[1,2,3],null |
array(integer) not null | no | yes | [],[1,2,3],[1,2,null] |
array(integer not null) not null | no | no | [],[1,2,3] |
The inner nullability of an array type can be changed by casting, using the conventional cast syntax.
Nullable to non-nullable:
Non-nullable to nullable:
A cast from a non-nullable element type to its nullable counterpart always succeeds. The reverse direction succeeds if the array doesn’t contain null elements. Casts across element types aren’t supported. The only exception is casts from array to string types.
Non-nullable element types use less memory and turn on optimizations for certain array operations. Use the most “restrictive” element type possible based on the use case at hand.
Arrays have these limitations.
- The size of an array is limited to 4GB.
- Arrays can’t be nested.
- Arrays can’t be used as column types in non-temporary tables.
See the restrictions regarding array support in external formats.