Добавление LIMIT к ARRAY_TO_JSON или ARRAY_AGG

В игре для двух игроков с использованием PostgreSQL 9.6.6 в качестве серверной части я определил следующую пользовательскую хранимую функцию для получения сообщений чата для пользователя:

CREATE OR REPLACE FUNCTION words_get_user_chat(
                in_uid integer
        ) RETURNS jsonb AS
$func$
        SELECT COALESCE(
                JSONB_OBJECT_AGG(gid, ARRAY_TO_JSON(y)),
                '{}'::jsonb
        ) FROM (
                SELECT  c.gid,
                        ARRAY_AGG(
                                JSON_BUILD_OBJECT(
                                        'created', EXTRACT(EPOCH FROM c.created)::int,
                                        'uid',     c.uid,
                                        'msg',     c.msg
                                )
                                ORDER BY c.created ASC
                        ) AS y
                FROM      words_chat c
                LEFT JOIN words_games g
                USING     (gid)
                WHERE     in_uid in (g.player1, g.player2)
                AND       (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
                GROUP BY  c.gid
                /* LIMIT 10 */
        ) AS x;

$func$ LANGUAGE sql STABLE;

Он объединяет таблицы words_games и words_chat и создает следующий JSON-объект (с идентификатором игры «9» в качестве строкового ключа), содержащий JSON-массив с сообщениями:

 # select words_get_user_chat(6);



                                                                                                                   words_get_user_chat                        




--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
 {"9": [{"msg": "test2", "uid": 6, "created": 1516445342}, {"msg": "test3", "uid": 6, "created": 1516445358}, {"msg": "test4", "uid": 6, "created": 1516445369
}, {"msg": "test5", "uid": 6, "created": 1516445381}, {"msg": "test6", "uid": 6, "created": 1516445405}, {"msg": "test7", "uid": 6, "created": 1516445415}, {"
msg": "test8", "uid": 6, "created": 1516445508}, {"msg": "test9", "uid": 6, "created": 1516445539}, {"msg": "test10", "uid": 6, "created": 1516445743}, {"msg"
: "test11", "uid": 6, "created": 1516445989}, {"msg": "test12", "uid": 6, "created": 1516446101}, {"msg": "test13", "uid": 6, "created": 1516446125}, {"msg": 
"test14", "uid": 6, "created": 1516446145}, {"msg": "test15", "uid": 6, "created": 1516446227}, {"msg": "test16", "uid": 6, "created": 1516446652}, {"msg": "t
est17", "uid": 6, "created": 1516446999}, {"msg": "test18", "uid": 6, "created": 1516447168}, {"msg": "test19", "uid": 6, "created": 1516447229}, {"msg": "tes
t20", "uid": 6, "created": 1516447493}, {"msg": "test21", "uid": 6, "created": 1516447532}, {"msg": "test22", "uid": 6, "created": 1516447555}, {"msg": "test2
3", "uid": 6, "created": 1516448017}, {"msg": "test24", "uid": 6, "created": 1516448062}]}
(1 row)

Это работает хорошо, но я хотел бы добавить LIMIT 10 к количеству элементов массива — как меру против флуда в чате.

Я попытался добавить его в функцию (см. строку с комментариями выше), но это не дало никакого эффекта.

Не могли бы вы предложить правильное место для LIMIT 10?

Я отправляю JSON-объекты через WebSockets в Android-приложение и хотел бы, чтобы злоумышленники не увеличивали размер таких объектов, заполняя чаты.

ОБНОВЛЕНИЕ:

Я пробую предложение Майка:

CREATE OR REPLACE FUNCTION words_get_user_chat(
                in_uid integer
        ) RETURNS jsonb AS
$func$
        SELECT COALESCE(
                JSONB_OBJECT_AGG(gid, ARRAY_TO_JSON(y)),
                '{}'::jsonb
        ) FROM (
                SELECT  c.gid,
                        ROW_NUMBER() OVER (PARTITION BY c.gid) AS rn,
                        ARRAY_AGG(
                                JSON_BUILD_OBJECT(
                                        'created', EXTRACT(EPOCH FROM c.created)::int,
                                        'uid',     c.uid,
                                        'msg',     c.msg
                                )
                                ORDER BY c.created ASC
                        ) AS y
                FROM      words_chat c
                LEFT JOIN words_games g
                USING     (gid)
                WHERE     in_uid in (g.player1, g.player2)
                AND       (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
                AND       rn < 10
                GROUP BY  c.gid
        ) AS x;

$func$ LANGUAGE sql STABLE;

но, к сожалению, получите синтаксическую ошибку:

ERROR:  42703: column "rn" does not exist
LINE 24:                 AND       rn < 10
                                   ^
LOCATION:  errorMissingColumn, parse_relation.c:3194

ОБНОВЛЕНИЕ 2:

Вот обе таблицы, которые я использую, извините, что не включил эту информацию раньше -

#\d words_chat
                                   Table "public.words_chat"
 Column  |           Type           |                        Modifiers                         
---------+--------------------------+----------------------------------------------------------
 cid     | bigint                   | not null default nextval('words_chat_cid_seq'::regclass)
 created | timestamp with time zone | not null
 gid     | integer                  | not null
 uid     | integer                  | not null
 msg     | text                     | not null
Indexes:
    "words_chat_pkey" PRIMARY KEY, btree (cid)
Foreign-key constraints:
    "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_chat_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE

# \d words_games
                                   Table "public.words_games"
  Column  |           Type           |                         Modifiers                         
----------+--------------------------+-----------------------------------------------------------
 gid      | integer                  | not null default nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone | 
 player1  | integer                  | not null
 player2  | integer                  | 
 played1  | timestamp with time zone | 
 played2  | timestamp with time zone | 
 state1   | text                     | 
 state2   | text                     | 
 hint1    | text                     | 
 hint2    | text                     | 
 score1   | integer                  | not null
 score2   | integer                  | not null
 hand1    | character(1)[]           | not null
 hand2    | character(1)[]           | not null
 pile     | character(1)[]           | not null
 letters  | character(1)[]           | not null
 values   | integer[]                | not null
 bid      | integer                  | not null
Indexes:
    "words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "words_games_check" CHECK (player1 <> player2)
    "words_games_score1_check" CHECK (score1 >= 0)
    "words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
    "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE
    "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE
    "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE

Alexander Farber 20.01.2018 источник

comment

используйте 1_ - Mike 20.01.2018

comment

Спасибо, Майк, но, к сожалению, я не знаю, как применить ваше предложение — посмотрите обновление. - Alexander Farber 20.01.2018

comment

pastebin.com/HLWm6ZHZ Так подходит? И нужно добавить ORDER BY: ROW_NUMBER() OVER (PARTITION BY c.gid ORDER BY xxxx) для получения последних или других необходимых записей - Mike 20.01.2018

Ответы (2)

arrow_upward
2
arrow_downward

Ваша функция должна работать так:

CREATE OR REPLACE FUNCTION words_get_user_chat(in_uid integer)
  RETURNS jsonb AS
$func$
   SELECT COALESCE(jsonb_object_agg(gid, y), '{}')
   FROM  (
      SELECT gid, jsonb_agg((SELECT j FROM (SELECT created, uid, msg) j)) AS y
      FROM  (
         SELECT DISTINCT gid  -- DISTINCT may be redundant
         FROM   words_games
         WHERE (finished IS NULL
             OR finished > (CURRENT_TIMESTAMP - INTERVAL '1 day'))
         AND    in_uid IN (player1, player2)
         ) g
      CROSS JOIN LATERAL (
         SELECT EXTRACT(EPOCH FROM created)::int AS created
              , uid
              , msg
         FROM   words_chat c
         WHERE  c.gid = g.gid
         ORDER  BY c.created DESC
         LIMIT  10                        --  HERE !!
         ) c 
      GROUP  BY 1
      ) x
$func$ LANGUAGE sql STABLE;

Не объединяйте все строки, чтобы потом отбросить лишнее. Было бы пустой тратой. Поместите LIMIT после ORDER BY в подзапрос.

Для этого вам нужно сначала идентифицировать подходящие gid из words_games, а затем использовать соединение LATERAL с подзапросом на words_chat. Должно быть правильно и быстрее.

Поскольку c.created определяется NOT NULL, вам не нужно добавлять NULLS LAST в предложение ORDER BY. Этот соответствующий многоколоночный индекс должен обеспечить наилучшую производительность чтения:

CREATE INDEX ON words_chat(gid, created DESC);

И, может быть, какой-нибудь указатель на words_games. Зависит от мощностей и частот значений.

Занимаясь этим, я также упростил построение результата jsonb.

Связанный:

Возврат в виде массива объектов JSON в SQL (Postgres )

Erwin Brandstetter 20.01.2018

comment

Это работает безупречно, спасибо! Как вы думаете, должен ли я добавить индекс для столбца created, потому что я использую его ORDER BY? (Я обновил свой вопрос описанием обеих таблиц). - Alexander Farber; 20.01.2018

arrow_upward
2
arrow_downward

Возможно, это отвечает на ваш вопрос. Попробуйте сделать:

(ARRAY_AGG( /* same as before */ ))[1:10] AS y

5ar 20.01.2018

comment

Работает, но существенно дороже, чем нужно. - Erwin Brandstetter; 20.01.2018

comment

К сожалению, он возвращает самые старые сообщения чата (от 1 до 10), а мне нужны самые последние 10. - Alexander Farber; 20.01.2018

comment

Затем измените ORDER BY на DESC - 5ar; 20.01.2018

comment

Хм, тогда мне нужно отсортировать его в своем приложении для Android, чего я не хотел делать... Я бы предпочел получить фрагмент с конца массива PostgreSQL, но пока не нашел способа: тест select (ARRAY[1,2,3,4,5,6])[-3:]; работает не так, как я ожидал.. . - Alexander Farber; 20.01.2018

Добавление LIMIT к ARRAY_TO_JSON или ARRAY_AGG

Ответы (2)

Вопросы по теме