How to aggregate two PostgreSQL columns to an array separated by brackets

Question

I would like to concatenate two columns using a group-by query resulting in an array separed with brackets. I know this question is related to this question, but as usual my use-case is a little different.

A simple example (also as SQL Fiddle). Currently my query returns the following:

ID  X   Y 3   0.5 2.71 3   1.0 2.50 3   1.5 2.33 6   0.5 2.73 6   1.5 2.77 

But where I would like concatenate/aggregate the X/Y columns to get the following:

ID  XY 3   [[0.5,2.71],[1.0,2.50],[1.5,2.33]] 6   [[0.5,2.73],[1.5,2.77]] 

Currently I've tried to concatenate the columns into one as follows:

SELECT "ID",concat_ws(', ',"X", "Y") as XY FROM Table1; 

Which returns:

ID  xy 3   0.5, 2.71 3   1, 2.50 3   1.5, 2.33 6   0.5, 2.73 

And used array_agg():

SELECT "ID",array_to_string(array_agg("X"),',') AS XY FROM Table1 GROUP BY "ID"; 

Resulting in:

ID  xy 3   0.5,1,1.5 6   0.5 

I feel I'm getting closer, but a helping hand would be really appreciated.

Answer 1

Create an array from the two columns, the aggregate the array:

select id, array_agg(array[x,y]) from the_table group by id; 

Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])