SQL – Expand column of arrays into column of elements

I have a SQL table, and one column of the table has type text[]. I want to create write a query that will create a new table, which consists of all arrays flattened and concatenated. Ex: If there are 3 items in the table, and the array entry for each of those items is [1, 2, 3], NULL, [1, 4, 5], I want the result set to be [1, 2, 3, 1, 4, 5].

UNNEST seems like it could be useful here. But, I can’t find a way to apply the function to every array in the table. Essentially, I want to “map” this function over every row in the table. Can anyone point me in a good direction?

CREATE TABLE arrs (
  col1 int,
  col2 text[]
);


INSERT INTO arrs (col1, col2) VALUES (1, '{"a", "b", "c"}');
INSERT INTO arrs (col1, col2) VALUES (2, '{"d", "e"}');

I want the query to return a table with 5 rows with text values “a”, “b”, “c”, “d”, “e” for the above table.

Useful REPL for testing: https://replit.com/languages/sqlite

Thanks!

Answer

Just expand all the arrays in the table (with UNNEST) and put into one common array (with ARRAY_AGG):

with t as (select unnest(col2) as elems
from arrs)
select array_agg(t.elems)
from t;

Here’s dbfiddle also

Advertisement

Answer