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 separated by brackets. I know this question is related to this question, but 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.

user330315 · Accepted Answer · 2016-08-29 18:47:34Z

72

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, 2016 at 18:47

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Mattijn Over a year ago

Thanks for your quick respond, it works perfect in postgresql (not in sqlfiddle)

Erwin Brandstetter Over a year ago

@Mattijn: Your version is obviously 9.5 or later. sqlfiddle has been stuck at 9.3 for some time now.

Shiv Krishna Jaiswal Over a year ago

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)

Erwin Brandstetter · Accepted Answer · 2024-10-24 08:05:24Z

Postgres 9.5 or later

An additional (overloaded) variant of array_agg() takes array input:

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.)

Older versions

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Postgres 9.5 or later

Older versions

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Postgres 9.5 or later

Older versions

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related