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
CodePudding user response:
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 ;