Как объединить два столбца PostgreSQL в массив, разделенный скобками

Question

Я хотел бы объединить два столбца с помощью группового запроса, в результате чего массив будет разделен скобками. Я знаю, что этот вопрос связан с этим вопросом, но, как обычно, мой вариант использования немного отличается.

Простой пример (также как SQL Fiddle). В настоящее время мой запрос возвращает следующее:

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

Но где я хотел бы объединить / агрегировать X/Y столбцы, чтобы получить следующее:

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

В настоящее время я попытался объединить столбцы в один следующим образом:

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

Который возвращает:

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

И использовал array_agg():

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

В результате чего:

ID  xy
3   0.5,1,1.5
6   0.5

Я чувствую, что становлюсь ближе, но рука помощи будет очень признательна.

Answer 1

Создайте массив из двух столбцов, агрегируйте массив:

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

Обратите внимание, что текстовое представление массивов по умолчанию использует фигурные скобки ({..}) не квадратные скобки ([..])

Answer 2

В Postgres 9,5 или позже array_agg() принимает массивы в качестве входных данных, чтобы разрешить простой синтаксис, предоставляемый @a_horse:

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

В старых версиях это еще не реализовано. Вы можете создать собственную агрегатную функцию (один раз) для достижения того же:

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

Затем:

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

Подробности:

• Выбор данных в массив Postgres

Или вы можете объединить строку:

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

Выдает желаемый результат точно. Строка, а не массив.