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]