Get distinct array values in a given column for an entire table

[bputt]

Use case: Getting the unique values of an array for an entire table

Feature description: Being able to run a SELECT DISTINCT statement against an array column.

Schema:

CREATE TABLE someTable (
    someArr array(STRING)
);

Example data:

INSERT INTO someTable(someArr) VALUES (["a", "b"]);
INSERT INTO someTable(someArr) VALUES (["b", "c"]);

SQL Statement:

SELECT DISTINCT someArr FROM someTable;

Currently throws: Cannot GROUP BY 'someArr': invalid data type 'string_array'

Would like for it to return something like:

a
b
c

[joemoe]

thanks @bputt for the suggestions, we put that onto our backlog. i'll keep you posted.

[mfussenegger]

We've merged support for table functions within the select list (#7716). This will be available in 3.2. Together with unnest this can be used for your example:

create table t (someArr array(string));
insert into t (someArr) values (['a', 'b']);
insert into t (someArr) values (['b', 'c']);
select distinct * from (select unnest(someArr) from t) tt;
+-----------------+
| unnest(somearr) |
+-----------------+
| a               |
| b               |
| c               |
+-----------------+

Note that it will be necessary to use a subquery for the unnest and do the aggregation/grouping on-top.