How to Combine Arrays in Snowsql GroupBy and Only Keep Distinct Values?

Question

I am trying to run a query that lists all the distinct markets a user has submitted to for my dataset.

The values in the Markets column are already in an array format. When I run the query below, I get an array of arrays and some markets may be listed multiple times because the distinct clause is looking at the unique arrays and not the values in the arrays.

For example if I'm trying to group ['New York'] and ['New York' , 'Chicago'], my goal is to get ['New York', 'Chicago'] as my result, but am currently getting [['New York'],['New York', 'Chicago']]. Appreciate any assistance.

SELECT 
  s.submitter_id,
  ARRAY_AGG(DISTINCT s.markets)
FROM 
  analytics.submissions AS s
GROUP BY 1

user_43992 · Answer

A simple way is to first flatten the array

WITH data AS (
 SELECT submitter_id, split(markets,';') AS markets 
 FROM VALUES (1,'new york'), (1,'new york;chicargo') s(submitter_id, markets)
)
SELECT 
  a.submitter_id,
  ARRAY_AGG(DISTINCT a.market)
FROM (
    SELECT s.submitter_id
        ,f.value AS market
    FROM data AS s,
    LATERAL FLATTEN(input => s.markets) f
) AS a
GROUP BY 1;

user_4744096 · Answer

A variation using a javascript UDF:

WITH data AS (
 SELECT submitter_id, split(markets,';') AS markets 
 FROM VALUES (1,'new york'), (1,'new york;chicargo') s(submitter_id, markets)
)
SELECT 
  submitter_id,
  array_flat_distinct(ARRAY_AGG(distinct markets))
from data
group by 1;

Where the UDF is defined as:

create or replace function array_flat_distinct("a" array)
returns array
language javascript
as
$$
    return [...new Set(a.reduce((b,c)=>[...b,...c]))]
$$
;

user_5070879 · Answer

It could be easily achieved with ARRAY_UNION_AGG aggregate function, which does exactly what is asked in the question:

Returns an ARRAY that contains the union of the distinct values from the input ARRAYs in a column.

For sample data:

CREATE TABLE submissions  AS
          SELECT 1 AS submitter_id, ['New York' , 'Chicago'] AS markets
UNION ALL SELECT 1 AS submitter_id, ['New York']             AS markets;

Query:

SELECT submitter_id, ARRAY_UNION_AGG(markets) AS markets
FROM submissions
GROUP BY submitter_id;

Output:

How to Combine Arrays in Snowsql GroupBy and Only Keep Distinct Values?

3 Answers