ARRAY

ARRAY is a data type used to represent a value composed of a sequence of elements of the same type. This data type can store a series of elements with the same data type, making it convenient for unified management and operation.

Syntax

ARRAY <elementType>

• elementType：Specifies the data type of the elements in the array.

ARRAY constant format

select [1,3,4];

This format represents an array constant. Please ensure that when defining an array constant, do not use quotes, as quotes will cause it to be recognized as a string type, and the string type '[1,3,4]' cannot be directly cast into the array<int> type.

Example

1. Create an integer array:

   > SELECT ARRAY(1, 2, 3);
   [1, 2, 3]
   

2. Convert an integer array to a string array:

   > SELECT CAST(ARRAY(1, 2, 3) AS ARRAY<STRING>);
   ["1", "2", "3"]
   

3. Get the data type of array elements:

   > SELECT typeof(ARRAY(1.2, 1.4, 1.8));
   array<decimal(2,1)>
   

4. Create a nested array (an array containing other arrays):

   > SELECT CAST(ARRAY(ARRAY(10, 20), ARRAY(30, 40)) AS ARRAY<ARRAY<DECIMAL(10,2)>>);
   [[10.00, 20.00], [30.00, 40.00]]
   

5. Get specific elements in an array:

   > SELECT a[0] FROM VALUES(ARRAY(10, 20, 30)) AS T(a);
   10
   

6. Use the IN operator to check if a specific element exists in an array:

   > SELECT 'Element exists' AS Result WHERE array_contains( ARRAY(1, 2, 3),3);
   Result
   --------
   Element exists
   

7. Sorting an array:

   > SELECT ARRAY_SORT(array(2, 1, 3));
   [1, 2, 3]
   

8. Merge Two Arrays:

   > SELECT array_union(array(2, 1, 3, 3), array(3, 5)); 
    [2,1,3,5]