P
P
Peter2017-03-23 15:44:02
PostgreSQL
Peter, 2017-03-23 15:44:02

How to form a selection of unique objects in an array?

The table has a unique id, there is a user id , an additional type value and a value from 1 to 6 that he chose.
id | id_user | type | data
----+-----------+-------
1 | 1456 | action | 6
2 | 5812 | usual | 2
3 | 1456 | present | 1
4 | 1456 | present | 1
5 | 5812 | usual |2
6 | 7774 | present | 1
I need to make a selection so that I get the user id and an array with unique objects, i.e. no duplicate entries.
The output should be:

[
    {
        id_user: 1456,
        data: [
                  {data: 6, type: action}, 
                  {data: 1, type: present}
                 ],
    },
    {
        id_user: 5812,
        data: [
                  {data:2, type: usual}
                 ]
    },
    {
        id_user: 7774,
        data: [
                 {data:1, type: present}
                 ]
    }
]

What should the query look like for this solution?
Thanks in advance!

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Melkij, 2017-03-23
@volkov_p_v

select json_agg(json_build_object('id_user', id_user, 'data', d)) from (
    select id_user, json_agg(json_build_object('data', val, 'type', type)) as d from (
        select id_user, type, val from tt group by id_user, type, val
    ) t group by id_user
) s;

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question