Flatten and aggregate two columns of arrays via distinct in Snowflake

user_3325742 0
Viewed 1327

Table structure is

+------------+---------+
|  Animals   |  Herbs  |
+------------+---------+
| [Cat, Dog] | [Basil] |
| [Dog, Lion]| []      |
+------------+---------+

Desired output (don't care about sorting of this list):

unique_things
+------------+
[Cat, Dog, Lion, Basil]

First attempt was something like

SELECT ARRAY_CAT(ARRAY_AGG(DISTINCT(animals)), ARRAY_AGG(herbs))

But this produces

[[Cat, Dog], [Dog, Lion], [Basil], []]

Since the distinct is operating on each array, not looking at distinct components within all arrays

snowflake-cloud-data-platform
3 Answers

UPDATE

It is possible without using FLATTEN, by using ARRAY_UNION_AGG:

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

For sample data:

CREATE OR REPLACE TABLE t AS
SELECT ['Cat', 'Dog'] AS Animals, ['Basil'] AS Herbs
UNION SELECT ['Dog', 'Lion'], [];

Query:

SELECT ARRAY_UNION_AGG(ARRAY_CAT(Animals, Herbs)) AS Result
FROM t

or:

SELECT ARRAY_UNION_AGG(Animals) AS Result
FROM (SELECT Animals FROM t 
      UNION ALL
      SELECT Herbs FROM t);

Output:

enter image description here

You could flatten the combined array and then aggregate back:

SELECT ARRAY_AGG(DISTINCT F."VALUE") AS unique_things
FROM tab, TABLE(FLATTEN(ARRAY_CAT(tab.Animals, tab.Herbs))) f
user_50708790

If I understand your requirements right and assuming the source tables of

insert into tabarray select array_construct('cat', 'dog'), array_construct('basil');
insert into tabarray select array_construct('lion', 'dog'), null;

I would say the result would look like this:

select array_agg(distinct value) from
(
  select
    value from tabarray
  , lateral flatten( input => col1 )
  union all
  select
  value from tabarray
  , lateral flatten( input => col2 ))
  ;
user_122009880

Here is another variation to handle NULLs in case they appear in data set.

SELECT ARRAY_AGG(DISTINCT a.VALUE) unique_things from tab, TABLE (FLATTEN(array_compact(array_append(tab.Animals, tab.Herbs)))) a
user_102129860
Related