SQL - Expand column of arrays into column of elements

Question

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

one column of the table has type string[] No such datatype in MySQL. Check your DBMS carefully and edit tags list accordingly. — Akina
– Akina, Commented Jun 3, 2022 at 5:42
@Akina correctly updated tag from MySQL to postgresql. Sorry for mistake! — Andy Delworth
– Andy Delworth, Commented Jun 3, 2022 at 5:49
I see the words about NULL values presence but do not see it in the script. And I don't see desired output. The most first question - does the elements order makes sense and must be saved? both in separate source array and in concatenated array (order by col1?). — Akina
– Akina, Commented Jun 3, 2022 at 6:17

Jesusbrother · Accepted Answer · 2022-06-03 06:11:04Z

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

Collectives™ on Stack Overflow

SQL - Expand column of arrays into column of elements

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related