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 61 · Accepted Answer · answered Aug 29 '16 at 18:47

61

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

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
3

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

Erwin Brandstetter · Answer 2 · 2023-05-09T21:20:39.150

In Postgres 9.5 or later a overloaded variant of array_agg() takes array input as provided by @a_horse.

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

The manual:

Concatenates all the input arrays into an array of one higher dimension. (The inputs must all have the same dimensionality, and cannot be empty or null.)

In older versions, this isn't implemented yet. You can create your own aggregate function (once per database) 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   tbl
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   tbl
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