Приветствую! Нужна помощь с 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 (в теги не пишу так как вроде вопрос не про сам постгрес).
Заранее спасибо!
