Return Array for each column (one query command)

Question

This is my People table, and I'm looking to get one array for each column as output.

name	surname
Cell 1	Cell 4
Cell 2	Cell 5
Cell 3	Null

SELECT array_agg(name) AS names FROM people

I only understand how to return one array from the table, but I would like to have one array for each column without any expression (comparing).

Im looking for a Result like this below:

((Cell1, Cell2, Cell3), (Cell4, Cell5))

It would be also fine to use different Tables instead of Columns. So turning two Queries below, into one

SELECT array_agg(name) FROM name
SELECT array_agg(surname) FROM surname

score 1 · Accepted Answer · answered Oct 31 '22 at 14:34

First of all : ((Cell1, Cell2, Cell3), (Cell4, Cell5)) is not the right notation for a sql array.

{{Cell1, Cell2, Cell3}, {Cell4, Cell5}} is the right notation for a text array but you will get the sql error "Multidimensional arrays must have sub-arrays with matching dimensions" because the first sub-array is of dimension 3 whereas the second sub-array is of dimension 2, which is not accepted for sql arrays.

So you have two solutions here :

Solution 1 : including NULL values so that both sub-arrays have the same dimension :

SELECT array(SELECT array_agg(name) FROM people_table UNION ALL SELECT array_agg(surname) FROM people_table) ;

The result is of type text[] :

array
{{"Cell 1","Cell 2","Cell 3"},{"Cell 4","Cell 5",NULL}}

Solution 2 : replacing the sql array by a json array which accepts sub-arrays with various dimensions while excluding the NULL values for the surname column :

SELECT json_build_array(array_agg(name), array_agg(surname) FILTER (WHERE surname IS NOT NULL)) AS array FROM people_table ;

The result is of type json :

array
[["Cell 1", "Cell 2", "Cell 3"], ["Cell 4", "Cell 5"]]

Last but not least, when name and surname come from two different tables :

-- warning: both sub queries must return the same number of rows !
SELECT array(SELECT array_agg(name) FROM name UNION ALL SELECT array_agg(surname) FROM surname) ;

or

-- notice: both sub queries may return different numbers of rows
SELECT json_build_array((SELECT to_json(array_agg(name)) FROM name), (SELECT to_json(array_agg(surname)) FROM surname WHERE surname IS NOT NULL)) AS array  ;

Return Array for each column (one query command)

1 Answers1