'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?

sqlsnowflake-cloud-data-platform


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lukasz Szozda

Related Questions