CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Andrei Budaes
Andrei Budaes

Reputation: 665

How to concatenate arrays in Snowflake with distinct values?

I have a MERGE scenario with an ARRAY_AGG() function in the main script. Once I have a match I'd like to combine the arrays from the source and target table but don't want to add existing values. I've looked at using ARRAY_CAT() but it doesn't return the expected output

This is the query:

SELECT
  ARRAY_CAT(ARRAY_CONSTRUCT('VALUE 1', 'VALUE 2'), ARRAY_CONSTRUCT('VALUE 1', 'VALUE 3')) arr_col

But this returns:

ARR_COL
[ "VALUE 1", "VALUE 2", "VALUE 1", "VALUE 3" ]

And what I need is :

ARR_COL
[ "VALUE 1", "VALUE 2", "VALUE 3" ]

Is there an easy way to achieve this except converting to string, removing the dups with regex then splitting to array?

Upvotes: 4

Views: 6021

Answers (4)

Nelson
Nelson

Reputation: 52

Use ARRAY_DISTINCT after ARRAY_CAT inline:

SELECT
  ARRAY_DISTINCT(ARRAY_CAT(ARRAY_CONSTRUCT('VALUE 1', 'VALUE 2'), ARRAY_CONSTRUCT('VALUE 1', 'VALUE 3'))) as arr_col

returns ["VALUE 1", "VALUE 2", "VALUE 3"]

Upvotes: 2

Brad
Brad

Reputation: 11

I modified it a bit so that the results were sorted as well:

 const myArr = Array.from(new Set([...ARRAY1,...ARRAY2]));
 myArr.sort()
 return myArr

Upvotes: 1

Andrei Budaes
Andrei Budaes

Reputation: 665

I've created an UDF in the end that allows me to do this as a scalar function

// Distinct Concatenate
create or replace function array_dcat(array1 variant, array2 variant)
    returns variant
    language javascript
    comment = 'Returns a distinct concatenation of two arrays'
as
$$
     return Array.from(new Set([...ARRAY1,...ARRAY2]));
$$
;

I used if as follows

SELECT
  ARRAY_CONSTRUCT('VALUE 1', 'VALUE 2') arr1,
  ARRAY_CONSTRUCT('VALUE 1', 'VALUE 3') arr2,
  ARRAY_CAT(arr1, arr2) sn_array_cat,
  ARRAY_DCAT(arr1, arr2) udf_array_dcat;

And returns

ARR1 ARR2 SN_ARRAY_CAT UDF_ARRAY_CAT
[ "VALUE 1", "VALUE 2" ] [ "VALUE 1", "VALUE 3" ] [ "VALUE 1", "VALUE 2", "VALUE 1", "VALUE 3" ] [ "VALUE 1", "VALUE 2", "VALUE 3" ]

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Using FLATTEN and ARRAY_AGG(DISTINCT):

SELECT ARRAY_AGG(DISTINCT F."VALUE") AS ARR_COL
FROM TABLE(FLATTEN(
     ARRAY_CAT(ARRAY_CONSTRUCT('VALUE 1', 'VALUE 2'), 
               ARRAY_CONSTRUCT('VALUE 1', 'VALUE 3'))
    )) f

Upvotes: 1

Related Questions