# Array functions
<a name="Array_Functions"></a>

This section describes the array functions for SQL supported in AWS Clean Rooms. 

**Topics**
+ [ARRAY function](array.md)
+ [ARRAY\$1CONTAINS function](array_contains.md)
+ [ARRAY\$1DISTINCT function](array_distinct.md)
+ [ARRAY\$1EXCEPT function](array_except.md)
+ [ARRAY\$1INTERSECT function](array_intersect.md)
+ [ARRAY\$1JOIN function](array_join.md)
+ [ARRAY\$1REMOVE function](array_remove.md)
+ [ARRAY\$1UNION function](array_union.md)
+ [EXPLODE function](explode.md)
+ [FLATTEN function](flatten.md)

# ARRAY function
<a name="array"></a>

Creates an array with the given elements.

## Syntax
<a name="array-synopsis"></a>

```
ARRAY( [ expr1 ] [ , expr2 [ , ... ] ] )
```

## Argument
<a name="array-argument"></a>

 *expr1, expr2*   
Expressions of any data type except date and time types. The arguments don't need to be of the same data type.

## Return type
<a name="array-return-type"></a>

The array function returns an ARRAY with the elements in the expression.

## Example
<a name="array-example"></a>

The following example shows an array of numeric values and an array of different data types.

```
--an array of numeric values
select array(1,50,null,100);
      array
------------------
 [1,50,null,100]
(1 row)

--an array of different data types
select array(1,'abc',true,3.14);
        array
-----------------------
 [1,"abc",true,3.14]
(1 row)
```

# ARRAY\$1CONTAINS function
<a name="array_contains"></a>

The ARRAY\$1CONTAINS function can be used to perform basic membership checks on array data structures. The ARRAY\$1CONTAINS function is useful when you need to check if a specific value is present within an array.

## Syntax
<a name="array_contains-syntax"></a>

```
array_contains(array, value)
```

## Arguments
<a name="array_contains-arguments"></a>

 *array*  
An ARRAY to be searched.

 *value*  
An expression with a type sharing a least common type with the array elements.

## Return type
<a name="array_contains-return-type"></a>

The ARRAY\$1CONTAINS function returns a BOOLEAN. 

If value is NULL, the result is NULL. 

If any element in array is NULL, the result is NULL if value is not matched to any other element.

## Examples
<a name="array_contains-example"></a>

The following example checks if the array `[1, 2, 3]` contains the value `4`. Since the array `[1, 2, 3`] doesn't contain the value `4`, the array\$1contains function returns `false`. 

```
SELECT array_contains(array(1, 2, 3), 4)
false
```

The following example checks if the array `[1, 2, 3]` contains the value `2`. Since the array `[1, 2, 3]` does contain the value `2`, the array\$1contains function returns `true`. 

```
SELECT array_contains(array(1, 2, 3), 2);
 true
```

# ARRAY\$1DISTINCT function
<a name="array_distinct"></a>

The ARRAY\$1DISTINCT function can be used to remove duplicate values from an array. The ARRAY\$1DISTINCT function is useful when you need to remove duplicates from an array and work with only the unique elements. This can be helpful in scenarios where you want to perform operations or analyses on a dataset without the interference of repeated values.

## Syntax
<a name="array_distinct-syntax"></a>

```
array_distinct(array)
```

## Arguments
<a name="array_distinct-arguments"></a>

 *array*  
An ARRAY expression.

## Return type
<a name="array_distinct-return-type"></a>

The ARRAY\$1DISTINCT function returns an ARRAY that contains only the unique elements from the input array. 

## Examples
<a name="array_distinct-example"></a>

In this example, the input array `[1, 2, 3, null, 3]` contains a duplicate value of `3`. The `array_distinct` function removes this duplicate value `3` and returns a new array with the unique elements: `[1, 2, 3, null]`.

```
SELECT array_distinct(array(1, 2, 3, null, 3));
 [1,2,3,null]
```

In this example, the input array `[1, 2, 2, 3, 3, 3]` contains duplicate values of `2` and `3`. The `array_distinct` function removes these duplicates and returns a new array with the unique elements: `[1, 2, 3]`.

```
SELECT array_distinct(array(1, 2, 2, 3, 3, 3))
  [1,2,3]
```

# ARRAY\$1EXCEPT function
<a name="array_except"></a>

The ARRAY\$1EXCEPT function takes two arrays as arguments and returns a new array that contains only the elements that are present in the first array but not the second array.

The ARRAY\$1EXCEPT is useful when you need to find the elements that are unique to one array compared to another. This can be helpful in scenarios where you need to perform set-like operations on arrays, such as finding the difference between two sets of data.

## Syntax
<a name="array_except-syntax"></a>

```
array_except(array1, array2)
```

## Arguments
<a name="array_except-arguments"></a>

 *array1*  
An ARRAY of any type with comparable elements.

 *array2*  
An ARRAY of elements sharing a least common type with the elements of *array1*.

## Return type
<a name="array_except-return-type"></a>

The ARRAY\$1EXCEPT function returns an ARRAY of matching type to *array1* with no duplicates.

## Examples
<a name="array_except-example"></a>

In this example, the first array `[1, 2, 3]` contains the elements 1, 2, and 3. The second array `[2, 3, 4]` contains the elements 2, 3, and 4. The `array_except` function removes the elements 2 and 3 from the first array, since they're also present in the second array. The resulting output is the array `[1]`.

```
SELECT array_except(array(1, 2, 3), array(2, 3, 4))
  [1]
```

In this example, the first array `[1, 2, 3]` contains the elements 1, 2, and 3. The second array `[1, 3, 5]` contains the elements 1, 3, and 5. The `array_except` function removes the elements 1 and 3 from the first array, since they're also present in the second array. The resulting output is the array `[2]`.

```
SELECT array_except(array(1, 2, 3), array(1, 3, 5));
 [2]
```

# ARRAY\$1INTERSECT function
<a name="array_intersect"></a>

The ARRAY\$1INTERSECT function takes two arrays as arguments and returns a new array that contains the elements that are present in both input arrays. This function is useful when you need to find the common elements between two arrays. This can be helpful in scenarios where you need to perform set-like operations on arrays, such as finding the intersection between two sets of data.

## Syntax
<a name="array_intersect-syntax"></a>

```
array_intersect(array1, array2)
```

## Arguments
<a name="array_intersect-arguments"></a>

 *array1*  
An ARRAY of any type with comparable elements.

 *array2*  
An ARRAY of elements sharing a least common type with the elements of array1.

## Return type
<a name="array_intersect-return-type"></a>

The ARRAY\$1INTERSECT function returns an ARRAY of matching type to array1 with no duplicates and elements contained in both array1 and array2.

## Examples
<a name="array_intersect-example"></a>

In this example, the first array `[1, 2, 3]` contains the elements 1, 2, and 3. The second array `[1, 3, 5]` contains the elements 1, 3, and 5. The ARRAY\$1INTERSECT function identifies the common elements between the two arrays, which are 1 and 3. The resulting output array is `[1, 3]`.

```
SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
 [1,3]
```

# ARRAY\$1JOIN function
<a name="array_join"></a>

The ARRAY\$1JOIN function takes two arguments: The first argument is the input array that will be joined. The second argument is the separator string that will be used to concatenate the array elements. This function is useful when you need to convert an array of strings (or any other data type) into a single concatenated string. This can be helpful in scenarios where you want to present an array of values as a single formatted string, such as for display purposes or for use in further processing.

## Syntax
<a name="array_join-syntax"></a>

```
array_join(array, delimiter[, nullReplacement])
```

## Arguments
<a name="array_join-arguments"></a>

 *array*  
Any ARRAY type, but its elements are interpreted as strings.

 *delimiter*  
A STRING used to separate the concatenated array elements.

 *nullReplacement*  
A STRING used to express a NULL value in the result.

## Return type
<a name="array_join-return-type"></a>

The ARRAY\$1JOIN function returns a STRING where the elements of array are separated by delimiter and null elements are substituted for `nullReplacement`. If `nullReplacement` is omitted, `null` elements are filtered out. If any argument is `NULL`, the result is `NULL`.

## Examples
<a name="array_join-example"></a>

In this example, the ARRAY\$1JOIN function takes the array `['hello', 'world']` and joins the elements using the separator `' '` (a space character). The resulting output is the string `'hello world'`. 

```
SELECT array_join(array('hello', 'world'), ' ');
 hello world
```

In this example, the ARRAY\$1JOIN function takes the array `['hello', null, 'world']` and joins the elements using the separator `' '` (a space character). The `null` value is replaced with the provided replacement string `','` (a comma). The resulting output is the string `'hello , world'`.

```
SELECT array_join(array('hello', null ,'world'), ' ', ',');
 hello , world
```

# ARRAY\$1REMOVE function
<a name="array_remove"></a>

The ARRAY\$1REMOVE function takes two arguments: The first argument is the input array from which the elements will be removed. The second argument is the value that will be removed from the array. This function is useful when you need to remove specific elements from an array. This can be helpful in scenarios where you need to perform data cleaning or preprocessing on an array of values.

## Syntax
<a name="array_remove-syntax"></a>

```
array_remove(array, element)
```

## Arguments
<a name="array_remove-arguments"></a>

 *array*  
An ARRAY.

 *element*  
An expression of a type sharing a least common type with the elements of array.

## Return type
<a name="array_remove-return-type"></a>

The ARRAY\$1REMOVE function returns the result type matched the type of the array. If the element to be removed is `NULL`, the result is `NULL`.

## Examples
<a name="array_remove-example"></a>

In this example, the ARRAY\$1REMOVE function takes the array `[1, 2, 3, null, 3]` and removes all occurrences of the value 3. The resulting output is the array `[1, 2, null]`. 

```
SELECT array_remove(array(1, 2, 3, null, 3), 3);
 [1,2,null]
```

# ARRAY\$1UNION function
<a name="array_union"></a>

The ARRAY\$1UNION function takes two arrays as arguments and returns a new array that contains the unique elements from both input arrays. This function is useful when you need to combine two arrays and eliminate any duplicate elements. This can be helpful in scenarios where you need to perform set-like operations on arrays, such as finding the union between two sets of data.

## Syntax
<a name="array_union-syntax"></a>

```
array_union(array1, array2)
```

## Arguments
<a name="array_union-arguments"></a>

 *array1*  
An ARRAY.

 *array2*  
An ARRAY of the same type as *array1*.

## Return type
<a name="array_union-return-type"></a>

The ARRAY\$1UNION function returns an ARRAY of the same type as array.

## Example
<a name="array_union-example"></a>

In this example, the first array `[1, 2, 3]` contains the elements 1, 2, and 3. The second array `[1, 3, 5]` contains the elements 1, 3, and 5. The ARRAY\$1UNION function combines the unique elements from both arrays, resulting in the output array `[1, 2, 3, 5]`. T

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

# EXPLODE function
<a name="explode"></a>

The EXPLODE function is used to transform a single row with an array or map column into multiple rows, where each row corresponds to a single element from the array or map.

## Syntax
<a name="explode-syntax"></a>

```
explode(expr)
```

## Arguments
<a name="explode-arguments"></a>

 *expr*  
An array expression or a map expression. 

## Return type
<a name="explode-return-type"></a>

The EXPLODE function returns a set of rows, where each row represents a single element from the input array or map.

The data type of the output rows depends on the data type of the elements in the input array or map. 

## Examples
<a name="explode-example"></a>

The following example takes the single-row array [10, 20] and transforms it into two separate rows, each containing one of the array elements (10 and 20).

```
SELECT explode(array(10, 20));
```

In the first example, the input array was directly passed as an argument to `explode()`. In this example, the input array is specified using the `=>` syntax, where the column name (`collection`) is explicitly provided.

```
SELECT explode(array(10, 20));
```

Both approaches are valid and achieve the same result, but the second syntax can be more useful when you need to explode a column from a larger dataset, rather than just a simple array literal.

# FLATTEN function
<a name="flatten"></a>

The FLATTEN function is used to "flatten" a nested array structure into a single flat array.

## Syntax
<a name="flatten-syntax"></a>

```
flatten(arrayOfArrays)
```

## Arguments
<a name="flatten-arguments"></a>

 *arrayOfArrays*  
An array of arrays.

## Return type
<a name="flatten-return-type"></a>

The FLATTEN function returns an array.

## Example
<a name="flatten-example"></a>

In this example, the input is a nested array with two inner arrays, and the output is a single flat array containing all the elements from the inner arrays. The FLATTEN function takes the nested array `[[1, 2], [3, 4]]` and combines all the elements into a single array `[1, 2, 3, 4]`.

```
SELECT flatten(array(array(1, 2), array(3, 4)));
 [1,2,3,4]
```