LINUX.ORG.RU

Аггрегация колонок с дальнейшим поиском в postgres

 


0

2

Приветствую! Нужна помощь с sql-запросом, надеюсь не пройдете мимо.

Есть три таблицы (упрощенно, по структуре у меня вопросов нет, ну и приведенное тут не отражает реальную структуру, просто передает важные части):

create table users (
    id int,
    username text
);

create table messages (
    id int,
    message text
)

create table usermessages (
    user int references users(id),
    message int references messages(id)
);

Мне нужно из этого получить id из users и массив с messages вида {"id": messages.id, "message": messages.message}, по одной строке на пользователя. И затем из всего иэтого найти пользователей с определенными сообщениями, при этом получив список всех сообщений пользователя в аггрегации.

Выбираю все вот так:

select
    users.id,
    json_agg(json_build_object('id', messages.id, 'message', messages.message))
from
    users
left outer join usermessages on
    usermessages.user. = users.id
left outer join messages on
    messages.id = usermesages.message
group by
    users.id;

Результаты примерно такие:

| id | json_agg |
| 1  | [{"id": 1, "message": "foo"}, {"id": 2, "message": "bar"}] |
| 2  | [{"id": 3, "message": "qwerty"}, {"id": 4, "message": "ololo"}] |
| 3  | [{"id": 5, "message": "1234"}] |
| 4  | [{"id": 6, "message": "foo"}] |

Вот в данном примере мне нужно получить все те записи, где есть message=«foo», то есть в результате должно быть

| id | json_agg |
| 1  | [{"id": 1, "message": "foo"}, {"id": 2, "message": "bar"}] |
| 4  | [{"id": 6, "message": "foo"}] |

Не могу понять как искать по аггрегации (даже имя этой колонки в where не впихнуть - в «as» он не умеет, а на «json_agg» (который у меня в результатах выводится) говорит что такой колонки нет), а в случае фильтров я выбираю вообще все записи, просто там где условие не выполняется получаю пустой массив. Отдельно выбирать по message по приджоиненной таблице - тогда я не вижу других message'й user'а.

В общем мне нужно что-то, что сможет пройтись по элементам массива, но ничего подобного нагуглить не получилось.

А, да, все это живет в PostgreSQL 10 (в теги не пишу так как вроде вопрос не про сам постгрес).

Заранее спасибо!

Ответ на: комментарий от theNamelessOne

Там не совсем про сообщения и пользователей, просто не хотелось усложнять описание. Да, один объект из messages может быть привязан к нескольким в users. Ну и один user может иметь связь с несколькими записями из messages.

micronekodesu ★★ ()
Последнее исправление: micronekodesu (всего исправлений: 1)

Не могу понять как искать по аггрегации

Используй having, Люк:

select
     users.id,
     jsonb_agg(jsonb_build_object('id', messages.id, 'message', messages.message))
 from
     users
 left outer join usermessages on
     usermessages."user" = users.id
 left outer join messages on
     messages.id = usermessages.message
 group by
     users.id
 having
     jsonb_agg(jsonb_build_object('id', messages.id, 'message', messages.message)) @> '[{ "message": "foo" }]'::jsonb;
theNamelessOne ★★★★★ ()
Ответ на: комментарий от theNamelessOne

Либо можно сделать фильтрацию по подзапросу.

select *
 from
   (select
       users.id,
       jsonb_agg(jsonb_build_object('id', messages.id, 'message', messages.message)) as messages
   from
       users
   left outer join usermessages on
       usermessages."user" = users.id
   left outer join messages on
       messages.id = usermessages.message
   group by
       users.id
   ) as subquery
 where
     subquery.messages @> '[{ "message": "foo" }]'::jsonb;
theNamelessOne ★★★★★ ()
Последнее исправление: theNamelessOne (всего исправлений: 1)

Вместо json в большинстве случаев лучше использовать jsonb, если для тебя не критична форма самого JSON-документа (т.е. порядок ключей в объектах, пробелы между ключами, сохранение дубликатов).

theNamelessOne ★★★★★ ()
Ответ на: комментарий от theNamelessOne

Я про

jsonb_agg(jsonb_build_object('id', messages.id, 'message', messages.message)) @> '[{ "message": "foo" }]'::jsonb;

Должно быть

jsonb_agg(jsonb_build_object('id', messages.id, 'message', messages.message))::jsonb @> '[{ "message": "foo" }]'::jsonb

Иначе ERROR: operator does not exist: json @> jsonb

(Ну и аналогичное в другом варианте)

И отдельное спасибо за комментарий про json и jsonb

micronekodesu ★★ ()
Ответ на: комментарий от micronekodesu

Должно быть

Нет.

jsonb_agg и так возвращает jsonb (оно даже в названии функции содержится), см. документацию, поэтому приводить результат к jsonb не нужно.

Скорее всего ты просто неправильно скопировал или перепечатал пример (например, использовал json_agg вместо jsonb_agg).

theNamelessOne ★★★★★ ()