How to concatenate arrays in Snowflake with distinct values?

Question

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?

Answer 1

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"]

Answer 2

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" ]