Array Constructor

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Creates an array from a comma-separated list of values.

1ARRAY[<element1>, <element2>, ..., <elementN>]

• <element1>, <element2>, ..., <elementN>: Values to include in the array. All elements must be of compatible types that can be coerced to a common element type.

Returns an ARRAY that contains the specified elements. Data 360 SQL automatically infers the array type from the provided elements.

• All array elements must be of compatible types.
• Elements can be literals, column references, or expressions.
• Empty arrays can be created with ARRAY[].
• Arrays can contain NULL values.
• Type coercion applies when elements are of different but compatible types.
• If no null values are in the constructor, Data 360 SQL infers the element type as not null.
• Arrays are limited to 4 GB in size
• Arrays cannot be nested (see Array Type).

Create a simple array of numeric values.

1SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

Returns [1,2,3,4,5]

Create an array from string literals.

1SELECT ARRAY['apple', 'banana', 'cherry'] AS fruits;

Returns ["apple","banana","cherry"]

Create an array from multiple columns in a query.

1SELECT
2  customer_id,
3  ARRAY[first_name__c, last_name__c] AS name_parts
4FROM Customer__dlm
5WHERE customer_id = '12345';

Use expressions within the array constructor.

1SELECT
2  product_name,
3  base_price,
4  ARRAY[base_price, base_price * 0.9, base_price * 0.8] AS price_tiers
5FROM Product__dlm;

Create an empty array with an explicit integer type.

1SELECT ARRAY[]::array(int) AS empty_array;

Returns []

Arrays can contain NULL elements.

1SELECT ARRAY[1, NULL, 3, NULL, 5] AS sparse_values;

Returns [1,NULL,3,NULL,5]

When elements are of different but compatible types, automatic type coercion occurs.

1SELECT ARRAY[1, 2.5, 3] AS mixed_numbers;

Returns [1.0,2.5,3.0] - all elements coerced to decimal type.

• Array Data Type - Complete documentation on array types including constructor syntax
• Array Functions - Overview of all array functions
• ARRAY_GENERATE_SERIES - Generate arrays programmatically
• Type Conversion - Type casting and coercion