Postgres aggregate results based on 2 columns

Question

In postgres 9.6 I have a table that includes these 3 columns.

id | val1 | val2
------------------
1  | x    | 1
1  | x    | 2
1  | x    | 3
1  | y    | 4
2  | y    | 1
2  | y    | 2

Can I use aggregate functions to turn it into this?

id | 
------------------------------
1  | { x: [1, 2, 3], y: [4] }
2  | { y: [1, 2] }

Or if what I'm looking for isn't possible, something like below could work too. Or anything that would allow me to transform the results into the above in my application code.

id | 
------------------------------
1  | [ {x: 1}, {x: 2}, {x: 3}, {y: 4} ]
2  | [ {y: 1}, {y: 2} ]

I know I can do something like select id, array_agg(val2) from mytable group by val2, but that only groups by val2 and returns something like

id | 
------------------
1  | [1, 2, 3, 4]
2  | [1, 2]

Answer 1

You can do it with:

SELECT
    id, json_object_agg(val1, aaa)
FROM
    (
    SELECT
        id, val1, json_agg(val2) AS aaa
    FROM
        t
    GROUP BY
        id, val1
    ) AS q 
GROUP BY
    id;

id | json_object_agg               
-: | :-----------------------------
 1 | { "x" : [1, 2, 3], "y" : [4] }
 2 | { "y" : [1, 2] }              

You can find the needed definitions to produce this result at dbfiddle here

You'll be using these two functions:

• json_agg
• json_object_agg

The documentation can be found at 9.20. Aggregate Functions in PostgreSQL doc