How to combine two arrays in Snowflake?

Post author:Vithal S
Post last modified:August 12, 2021
Post category:Snowflake
Reading time:4 mins read

Snowflake is the one of the databases that combines many useful functions from other relational databases such as Oracle, PostgreSQL, Teradata, etc. Like many other relational databases, Snowflake support many array functions. In this article, we will check how to combine/merge/concatenate two or more arrays in Snowflake.

Combine Two Arrays in Snowflake

Snowflake allows you to deal with many different kinds of data sets. For example, you can work with JSON, XML or array variables with an ease. As Snowflake integrates many heterogeneous data sets, you may get requirement such as combining two array variable or merge json variables. Snowflake supports many built-in functions to work with data sets such as json, xml, etc.

Combine Two Arrays in Snowflake using ARRAY_CAT Function

As discussed in my other article, Snowflake array functions, you can use ARRAY_CAT() function merge two array variables in the Snowflake cloud data warehouse.

Following is the syntax of ARRAY_CAT() function in Snowflake.

ARRAY_CAT( <array1> , <array2> )

ARRAY_CAT accepts two array variables or columns as an argument. This function will combine, concatenate or merge two array variables.

Test Data

Following test data will be used in all subsequent examples

SELECT * FROM arr_table;
+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
| [    | [    | [    |
|   1, |   3, |   5, |
|   2  |   4  |   6  |
| ]    | ]    | ]    |
+------+------+------+

Examples to Merge Array Columns and Variables

Consider following example to combine two arrays using ARRAY_CAT() function.

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

Similarly, you can apply an array_cat function on table columns as well

SELECT ARRAY_CAT(COL1,COL2) as arr_combine FROM arr_table;
+-------------+
| ARR_COMBINE |
|-------------|
| [           |
|   1,        |
|   2,        |
|   3,        |
|   4         |
| ]           |
+-------------+

Combine Two or more Arrays in Snowflake

Snowflake ARRAY_CAT function supports only two arguments. If your requirement is to combine two or more array columns or variable, then you can use the nested ARRAY_CAT function.

Consider following nested ARRAY_CAT function to combine three array columns. You can nest array_cat as many times as you want.

SELECT ARRAY_CAT(COL1,ARRAY_CAT(COL2, COL3)) as arr_combine FROM arr_table;
+-------------+
| ARR_COMBINE |
|-------------|
| [           |
|   1,        |
|   2,        |
|   3,        |
|   4,        |
|   5,        |
|   6         |
| ]           |
+-------------+