Snowflake Array Functions

It is very common practice to store values in the form of an array in the databases. Without a doubt, Snowflake supports many array functions. You can use these array manipulation functions to manipulate the array types. In this article, we will check how to work with Snowflake Array Functions, syntax and examples to manipulate array types.

Following is the list of Snowflake array functions with brief descriptions:

Array Functions	Description
ARRAY_AGG	Function returns the input values, pivoted into an ARRAY.
ARRAY_APPEND	This function returns an array containing all elements from the source array as well as the new element.
ARRAY_CAT	Concatenates two arrays.
ARRAY_COMPACT	This function can be used to convert sparse arrays into dense arrays. Returns a compacted array with missing and null values removed
ARRAY_CONSTRUCT	This function constructs array from zero, one, or more inputs.
ARRAY_CONSTRUCT_COMPACT	This function constructs array from zero, one, or more inputs without any NULL input values.
ARRAY_CONTAINS	This function returns True if the specified variant is found in the specified array.
ARRAY_INSERT	This function inserts new element to the source array and return it.
ARRAY_INTERSECTION	This function returns an array that contains the matching elements in the two input arrays.
ARRAY_POSITION	This function returns the index of the first occurrence of an element in an array.
ARRAY_PREPEND	Inserts new element at the beginning of the source array and returns it.
ARRAY_SIZE	Returns the size of the input array.
ARRAY_SLICE	Return subset of source array in the form of new array.
ARRAY_TO_STRING	Returns an input array converted to a string by casting all values to strings and concatenates them using the input separator.
ARRAYS_OVERLAP	This function compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element; otherwise returns FALSE.

Snowflake Array Functions Examples

Following are few examples on array function usage.

How to create an array in Snowflake?

You can use the array_construct function to create an array in Snowflake

For example,

select array_construct(10, 20, 30) as arr;
+-------+
| ARR   |
|-------|
| [     |
|   10, |
|   20, |
|   30  |
| ]     |
+-------+

How to check if element present in Snowflake array?

You can use the array_contains function to check if element present in an array.

For example,

select array_contains(10::variant, array_construct(10, 20, 30)) as arr;
+------+
| ARR  |
|------|
| True |
+------+

Note that, the array type is a semi-structured data and it is stored invariant data type. You have to explicitly convert a searching value to variant type.

How to insert new element to Snowflake array?

You can use the ARRAY_INSERT function to add an element to array object.

select array_insert(array_construct(0,1,2,3),2,'new') as arr;
+----------+
| ARR      |
|----------|
| [        |
|   0,     |
|   1,     |
|   "new", |
|   2,     |
|   3      |
| ]        |
+----------+

Note that, second parameter is a position where you want to add new element.

How to return the index of a Snowflake array element?

You can use the array_position function to return an index of the array element.

For example,

select array_position('two'::variant, array_construct('one', 'two')) as arr;
+-----+
| ARR |
|-----|
|   1 |
+-----+

How to append an element at the beginning of the Snowflake array?

You can use the array_prepend function to append an element at the beginning of the Snowflake array.

For example,

select array_prepend(array_construct(0,1,2,3),'first') as arr;
+------------+
| ARR        |
|------------|
| [          |
|   "first", |
|   0,       |
|   1,       |
|   2,       |
|   3        |
| ]          |
+------------+

How to identify the size of the Snowflake array?

You can use the ARRAY_SIZE function to identify the Snowflake array size.

For example,

select array_size(array_construct(0, 1, 2, 3)) as size;
+------+
| SIZE |
|------|
|    4 |
+------+

How to return a subset of the Snowflake array?

You can use the array_slice function to slice Snowflake array.

For example,

select array_slice(array_construct(0,1,2,3,4,5,6), 0, 2) as arr_slice;
+-----------+
| ARR_SLICE |
|-----------|
| [         |
|   0,      |
|   1       |
| ]         |
+-----------+

How to convert Snowflake array to string?

You can use the array_to_string function to convert Snowflake array to string.

For example,

select array_to_string(array_construct(0,1,2,3,4,5,6),',') as str;
+---------------+
| STR           |
|---------------|
| 0,1,2,3,4,5,6 |
+---------------+

How to append an element at the end of the Snowflake array?

You can use the array_append function to append an element at the end of the Snowflake array.

select array_append(array_construct(1, 2, 3), 'last') as arr;
+----------+
| ARR      |
|----------|
| [        |
|   1,     |
|   2,     |
|   3,     |
|   "last" |
| ]        |
+----------+

How to Concatenate two arrays in Snowflake?

You can use the array_cat function to concatenate two arrays.

For example,

select array_cat(array_construct(1, 2), array_construct(3, 4)) as arr_concat;
+------------+
| ARR_CONCAT |
|------------|
| [          |
|   1,       |
|   2,       |
|   3,       |
|   4        |
| ]          |
+------------+

How to remove NULL values from Snowflake array?

You can use the array_compact function to remove NULL and blank values from Snowflake array.

For example,

select array_construct(10, null, 30);
+-------------------------------+
| ARRAY_CONSTRUCT(10, NULL, 30) |
|-------------------------------|
| [                             |
|   10,                         |
|   undefined,                  |
|   30                          |
| ]                             |
+-------------------------------+

select array_compact(array_construct(10, null, 30)) as arr;
+-------+
| ARR   |
|-------|
| [     |
|   10, |
|   30  |
| ]     |
+-------+

How to get last element from Snowflake array?

You can use the array[len( array )-1] approach to return the last element of an array variable with unknown size.

For example,

WITH tmp
     AS (SELECT Array_construct('element1', 'element2', 'element3', 'element4')
                AS a)
SELECT a[array_size(a) - 1]
FROM   tmp; 
+--------------+
| LAST_ELEMENT |
|--------------|
| "element4"   |
+--------------+