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.

score 47 · Accepted Answer · answered Aug 29 '16 at 18:47

47

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 ([..])

answered Aug 29 '16 at 18:47

a_horse_with_no_name

497,550
91
775
843

Thanks for your quick respond, it works perfect in postgresql (not in sqlfiddle) – Mattijn Aug 29 '16 at 20:03
1

@Mattijn: Your version is obviously 9.5 or later. sqlfiddle has been stuck at 9.3 for some time now. – Erwin Brandstetter Aug 30 '16 at 02:30
2

Also, type of x and y should be same, i.e if x is of text, then y should also be of type text. If not, need to use type case.(::text) – Shiv Krishna Jaiswal Jan 08 '18 at 08:25

score 9 · Answer 2 · edited May 23 '17 at 12:00

In Postgres 9.5 or later array_agg() takes arrays as input to allow the simple syntax provided by @a_horse:

SELECT id, array_agg(ARRAY[x, y]) AS xy
FROM   Table1
GROUP  BY id;

In older versions, this isn't implemented yet. You can create your own aggregate function (once) to achieve the same:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
  , STYPE     = anyarray
  , INITCOND  = '{}'
);

Then:

SELECT id, array_agg_mult(ARRAY[ARRAY[x,y]]) AS xy  -- note the 2D array
FROM   Table1
GROUP  BY id;

Details:

Selecting data into a Postgres array

Or you can concatenate a string:

SELECT id, '[[' || string_agg(concat_ws(',', x, y), '],[') || ']]' AS xy
FROM   Table1
GROUP  BY id;

Produces your desired result exactly. A string, not an array.

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

2 Answers2