Is there a way to join all arrays in clickhouse column and then filter for duplicates?

Question

I have a clickhouse table with one of it columns being Array(T). When I run SELECT array_field FROM my_table I get the following:

1 | {a, b, c}
--------------
2 | {a, b}
--------------
3 | {d, e}

I need to find a way to get a list of unique values in all of that lists, just like that:

{a, b, c, d, e}

How can I do that?

Scintillation

Answer 1

To get the same in array in one row: use groupUniqArray with -Array combinator. Check docs

SELECT *
FROM my_table 

┌─array_field───┐
│ ['a','b','c'] │
│ ['a','b']     │
│ ['d','e']     │
└───────────────┘

3 rows in set. Elapsed: 0.001 sec. 

SELECT DISTINCT arrayJoin(array_field)
FROM my_table 

┌─arrayJoin(array_field)─┐
│ a                      │
│ b                      │
│ c                      │
│ d                      │
│ e                      │
└────────────────────────┘

SELECT groupUniqArrayArray(array_field)
FROM my_table 

┌─groupUniqArrayArray(array_field)─┐
│ ['c','e','d','a','b']            │
└──────────────────────────────────┘

Schechter

Answer 2

Another solution to your problem

SELECT arrayDistinct(arrayFlatten(groupArray(array_field)))
FROM my_table 

Shutdown

Answer 3

Found a solutions that works for me:

SELECT DISTINCT arrayJoin(array_field)
FROM my_table

Scintillation