Как сформировать выборку в массив из повторяющихся записей?

Question

[Пётр]

В таблице есть уникальный id, есть id пользователя и значение от 1 до 6, которое он выбрал.
id | id_user | data
----+-----------+-------
1 | 1456 | 6
2 | 5812 | 2
3 | 1456 | 1
4 | 1456 | 5
5 | 5812 | 3
6 | 7774 | 1

Мне надо сделать выборку так, что бы получилось id пользователя и массив всех выбранных им значений за все время.

[
    {
        id_user: 1456,
        data: [6, 1, 5],
    },
    {
        id_user: 5812,
        data: [2,3]
    },
    {
        id_user: 7774,
        data: [1]
    }
]

Как должен выглядеть запрос для данного решения?
Заранее спасибо!

Answer 1

[Melkij]

select id_user, array_agg(data) from tablename group by id_user

Можно и сразу json собрать. С оглядкой на версию базы только.

select json_agg((
select json_build_object('id_user', id_user, 'data', json_agg(data)) from tablename group by id_user
));

Comments

[Пётр]

Вместо array_agg мне подошел jsonb_agg . Большое спасибо, что помогли :)

[Пётр]

Столкнулся с такой проблемой, у некоторых пользователей повторяются ответы, т.е. выглядет так:
1 | 1456 | 6
2 | 5812 | 2
3 | 1456 | 1
4 | 1456 | 1
5 | 5812 | 2
6 | 7774 | 1

При описанным выше подходе получается, что массиве data есть дублированные значения. Как сделать так, что бы в них были только уникальные значения?

[Melkij]

json_agg(distinct data)
или group by id_user, data
Глянуть explain'ом, что будет интереснее.

[Пётр]

Melkij: спасибо distinct делает то что нужно, но при формировании объекта jsonb_agg(distinct json_build_object("id", id, "name", name) ) он не отрабатывает, и пишет ошибку :(

[Melkij]

Ну да, не для всего можно сделать distinct. А что сделать пытаетесь? Уже далеко от изначального вопроса.

[Пётр]

Melkij: данные надо выводить из игровой статистики. Долго объяснять, поэтому проще использовать такие примеры.
В системе игрок ставит за какое-то действия оценки, так получается, что за одно и то же действие может несколько раз поставить одну и ту же оценку, а мне в статистки надо вывести массив с объектами где есть уникальные оценки (не дублированные) и id игрока. В другую статистику надо было просто массив оценок игрока выводить, но с этим мы справились =) .

[Melkij]

Всё-таки не понял, как из этого получился json agg distinct по json object'у. Вместо как максимум json object по json_agg distinct

Answer 2

[Максим Федоров]

SELECT  id_user, array_agg(data) FROM mytable GROUP BY id_user

Comments

[Пётр]

Вместо array_agg мне подошел jsonb_agg . Большое спасибо, что помогли :)