Salesforce Docs Portal

Array Constructor

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

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

Syntax

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

Arguments

Required

  • <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

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

Considerations

  • 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).

Examples

Create an Array of Integers

Create a simple array of numeric values.

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

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

Create an Array of Strings

Create an array from string literals.

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

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

Array from Column Values

Create an array from multiple columns in a query.

SELECT
  customer_id,
  ARRAY[first_name__c, last_name__c] AS name_parts
FROM Customer__dlm
WHERE customer_id = '12345';

Array with Expressions

Use expressions within the array constructor.

SELECT
  product_name,
  base_price,
  ARRAY[base_price, base_price * 0.9, base_price * 0.8] AS price_tiers
FROM Product__dlm;

Empty Array

Create an empty array with an explicit integer type.

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

Returns []

Array with NULL Values

Arrays can contain NULL elements.

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

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

Type Coercion

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

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

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

Related Documentation