CODEWITHSUNDEEP
sqlarrayssnowflake-cloud-data-platform
bayesian.asian
bayesian.asian

Reputation: 299

How do I merge/combine/add two arrays in SQL to form a new array?

I have a table that contains two columns which stores data in array format. For example, column_1 contains values like [1,2,3,4] and column_2 contains values like [5,6,7]. I want to combine these two in order to create a column_3 who's value here would be the array [1,2,3,4,5,6,7]. The numbers will be unique, and the length of the arrays can be different.

Upvotes: 1

Views: 3347

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

ARRAY_CAT is used to concatenate two arrays, removal of duplicates could be achieved by ARRAY_DISTINCT:

SELECT col1, col2,
       ARRAY_CAT(col1, col2),
       ARRAY_DISTINCT(ARRAY_CAT(col1, col2))
FROM tab;

For sample data:

CREATE OR REPLACE TABLE tab(col1 ARRAY, col2 ARRAY)
AS
SELECT '[1,2,3,4]', '[5,6,7]' UNION
SELECT '[1,2,2]',   '[1,3]'   UNION
SELECT '[NULL, 1]', '[NULL]';

Output:

enter image description here

Upvotes: 1

Andrii Soldatenko
Andrii Soldatenko

Reputation: 567

does it work for you?

select array_cat(array_construct(NULL, 2), array_construct(3, 4));
+------------------------------------------------------------+
| ARRAY_CAT(ARRAY_CONSTRUCT(NULL, 2), ARRAY_CONSTRUCT(3, 4)) |
|------------------------------------------------------------|
| [                                                          |
|   undefined,                                               |
|   2,                                                       |
|   3,                                                       |
|   4                                                        |
| ]                                                          |
+------------------------------------------------------------+

But if you see such error:

select array_cat(NULL, array_construct(3, 4));
100072 (22000): NULL result in a non-nullable column

You can simply add:

select array_cat(IFNULL(NULL, array_construct()), array_construct(3, 4));
+-------------------------------------------------------------------+
| ARRAY_CAT(IFNULL(NULL, ARRAY_CONSTRUCT()), ARRAY_CONSTRUCT(3, 4)) |
|-------------------------------------------------------------------|
| [                                                                 |
|   3,                                                              |
|   4                                                               |
| ]                                                                 |
+-------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.241s

Note: Replace ARRAY_CONSTRUCT() to you column name.

Upvotes: 1

Related Questions