Выбор объекта JSON из массивов из таблицы PostgreSQL

Question

Я подготовил простой SQL Fiddle, демонстрирующий мою проблему -

В игре с двумя игроками пользовательские чаты хранятся в таблице:

CREATE TABLE chat(
    gid integer,            /* game id */
    uid integer,            /* user id */
    created timestamptz,
    msg text
);

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

INSERT INTO chat(gid, uid, created, msg) VALUES
    (10, 1, NOW() + interval '1 min', 'msg 1'),
    (10, 2, NOW() + interval '2 min', 'msg 2'),
    (10, 1, NOW() + interval '3 min', 'msg 3'),
    (10, 2, NOW() + interval '4 min', 'msg 4'),
    (10, 1, NOW() + interval '5 min', 'msg 5'),
    (10, 2, NOW() + interval '6 min', 'msg 6'),
    (20, 3, NOW() + interval '7 min', 'msg 7'),
    (20, 4, NOW() + interval '8 min', 'msg 8'),
    (20, 4, NOW() + interval '9 min', 'msg 9');

И я могу получить данные, выполнив запрос SELECT:

SELECT ARRAY_TO_JSON(
  COALESCE(ARRAY_AGG(ROW_TO_JSON(x)), 
           array[]::json[])) FROM (
SELECT
    gid,
    uid,
    EXTRACT(EPOCH FROM created)::int AS created,
    msg
FROM chat) x;

который возвращает мне JSON-массив:

[{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
 {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
 {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
 {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
 {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
 {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
 {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
 {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
 {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]

Это близко к тому, что мне нужно, однако я хотел бы использовать "gid" в качестве свойств объекта JSON, а остальные данные в качестве значений в этом объекте:

{"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
        {"uid":2,"created":1514813103,"msg":"msg 2"},
        {"uid":1,"created":1514813163,"msg":"msg 3"},
        {"uid":2,"created":1514813223,"msg":"msg 4"},
        {"uid":1,"created":1514813283,"msg":"msg 5"},
        {"uid":2,"created":1514813343,"msg":"msg 6"}],

 "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
        {"uid":4,"created":1514813463,"msg":"msg 8"},
        {"uid":4,"created":1514813523,"msg":"msg 9"}]}

Это возможно сделать с помощью функций PostgreSQL JSON?

Answer 1

Я думаю, что вы ищете json_object_agg для этого последнего шага. Вот как я это сделаю:

SELECT  json_object_agg(
          gid::text, array_to_json(ar)
        )
FROM    (
  SELECT  gid,
          array_agg(
            json_build_object(
              'uid', uid,
              'created', EXTRACT(EPOCH FROM created)::int,
              'msg', msg)
          ) AS ar
  FROM    chat
  GROUP BY gid
) x
;

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