Using SQL I'd like to convert a table that looks like this
| id | col11 | col2 |
|---|---|---|
| 1 | a | b |
| 1 | c | d |
| 2 | e | f |
| 2 | g | h |
Into something that looks like this:
| id | combined |
|---|---|
| 1 | [{col1: a, col2:b}, {col1: c, col2:d}] |
| 1 | [{col1: e, col2:f}, {col1: g, col2:h}] |
CodePudding user response：
We can try to use json_build_object function to build a JSON object out of a variadic argument list then use json_agg function.
SELECT id,
json_agg(json_build_object('col1',col11,
'col2',col2)) combined
FROM t
GROUP BY id