Как установить правильные имена атрибутов в агрегированный результат json с предложением GROUP BY?

У меня есть таблица temp, определенная следующим образом:

id |  name  |  body  | group_id
-------------------------------
1  | test_1 | body_1 | 1
2  | test_2 | body_2 | 1
3  | test_3 | body_3 | 2
4  | test_4 | body_4 | 2

Я хотел бы создать результат, сгруппированный по group_id и агрегированный для json. Однако запрос такой:

SELECT group_id, json_agg(ROW(id, name, body)) FROM temp
GROUP BY group_id;

Производит этот результат:

1;[{"f1":1,"f2":"test_1","f3":"body_1"}, 
   {"f1":2,"f2":"test_2","f3":"body_2"}]
2;[{"f1":3,"f2":"test_3","f3":"body_3"}, 
   {"f1":4,"f2":"test_4","f3":"body_4"}]

Атрибуты в объектах json называются f1, f2, f3 вместо id, name, body по мере необходимости. Я знаю, что можно правильно их использовать, используя подзапрос или общее табличное выражение, например:

SELECT json_agg(r.*) FROM (
  SELECT id, name, body FROM temp
) r;

Что дает этот результат:

[{"id":1,"name":"test_1","body":"body_1"}, 
 {"id":2,"name":"test_2","body":"body_2"}, 
 {"id":3,"name":"test_3","body":"body_3"}, 
 {"id":4,"name":"test_4","body":"body_4"}]

Но я, честно говоря, не вижу способа использовать его в сочетании с агрегацией. Что мне не хватает?

Ответы

Ответ 1

Для этого вам не нужна таблица temp или тип, но это не красиво.

SELECT json_agg(row_to_json( (SELECT r FROM (SELECT id, name, body) r) )) 
FROM t
GROUP BY group_id;

Здесь мы используем два подзапроса - сначала построим результирующий набор только с тремя нужными столбцами, затем внешним подзапросом, чтобы получить его как составной тип строки.

Он все равно будет работать нормально.

Чтобы это было сделано с менее уродливым синтаксисом, PostgreSQL должен был позволить вам устанавливать псевдонимы для анонимных типов строк, например, следующий (недействительный) синтаксис:

SELECT json_agg(row_to_json( ROW(id, name, body) AS (id, name, body) )) 
FROM t
GROUP BY group_id;

или нам нужен вариант row_to_json, который принял псевдонимы столбцов, например (снова недействителен):

SELECT json_agg(row_to_json( ROW(id, name, body), ARRAY['id', 'name', 'body'])) 
FROM t
GROUP BY group_id;

либо/оба из которых были бы хороши, но в настоящее время не поддерживаются.

Ответ 2

В Postgres 9.4 вы можете использовать json_build_object().

В вашем примере это работает как:

SELECT group_id, 
       json_agg(json_build_object('id', id, 'name', name, 'body', body)) 
FROM temp
GROUP BY group_id;

Это более дружелюбный способ, Postgres любит нас: 3

Ответ 3

Основываясь на @Craig answer, чтобы сделать его более изящным, здесь составной тип строки построен в списке from

select json_agg(row_to_json(s))
from
    t
    cross join lateral 
    (select id, name, body) s
group by group_id;
                                       json_agg                                       
--------------------------------------------------------------------------------------
 [{"id":1,"name":"test_1","body":"body_1"}, {"id":2,"name":"test_2","body":"body_2"}]
 [{"id":3,"name":"test_3","body":"body_3"}, {"id":4,"name":"test_4","body":"body_4"}]

Ответ 4

Я просто собирался опубликовать очень похожее решение на ваш, используя временную таблицу

create table t (
    id int,
    name text,
    body text,
    group_id int
);
insert into t (id, name, body, group_id) values
(1, 'test_1', 'body_1', 1),
(2, 'test_2', 'body_2', 1),
(3, 'test_3', 'body_3', 2),
(4, 'test_4', 'body_4', 2);

create temporary table tt(
    id int,
    name text,
    body text
);

select group_id, json_agg(row(id, name, body)::tt)
from t
group by group_id;
 group_id |                  json_agg                   
----------+---------------------------------------------
        1 | [{"id":1,"name":"test_1","body":"body_1"}, +
          |  {"id":2,"name":"test_2","body":"body_2"}]
        2 | [{"id":3,"name":"test_3","body":"body_3"}, +
          |  {"id":4,"name":"test_4","body":"body_4"}]

Ответ 5

Если вам нужны все поля из таблицы, вы можете использовать этот подход:

SELECT 
    group_id, json_agg(temp.*) 
FROM 
    temp
GROUP BY 
    group_id;

Ответ 6

Хорошо, отвечая на мой вопрос через пару минут после того, как я спрошу, но я нашел способ... Я просто не знаю, что это лучший. Я решил это, создав композитный тип:

CREATE TYPE temp_type AS (
  id bigint,
  name text,
  body text
);

И переписывая мой запрос, добавив приведение к типу:

SELECT group_id, json_agg(CAST(ROW(id, name, body) AS temp_type)) FROM temp
GROUP BY group_id;

Что вызвало ожидаемый результат:

1;[{"id":1,"name":"test_1","body":"body_1"}, 
   {"id":2,"name":"test_2","body":"body_2"}]
2;[{"id":3,"name":"test_3","body":"body_3"}, 
   {"id":4,"name":"test_4","body":"body_4"}]