How to set correct attribute names to a json aggregated result with GROUP BY clause?

Question

I have a table temp defined like this:

id |  name  |  body  | group_id
-------------------------------
1  | test_1 | body_1 | 1
2  | test_2 | body_2 | 1
3  | test_3 | body_3 | 2
4  | test_4 | body_4 | 2

I would like to produce a result grouped by group_id and aggregated to json. However, query like this:

SELECT group_id, json_agg(ROW(id, name, body)) FROM temp
GROUP BY group_id;

Produces this result:

1;[{"f1":1,"f2":"test_1","f3":"body_1"}, 
   {"f1":2,"f2":"test_2","f3":"body_2"}]
2;[{"f1":3,"f2":"test_3","f3":"body_3"}, 
   {"f1":4,"f2":"test_4","f3":"body_4"}]

The attributes in the json objects are named f1, f2, f3 instead of id, name, body as required. I know it is possible to alias them properly by using a subquery or a common table expression, for example like this:

SELECT json_agg(r.*) FROM (
  SELECT id, name, body FROM temp
) r;

Which produces this result:

[{"id":1,"name":"test_1","body":"body_1"}, 
 {"id":2,"name":"test_2","body":"body_2"}, 
 {"id":3,"name":"test_3","body":"body_3"}, 
 {"id":4,"name":"test_4","body":"body_4"}]

But I honestly don't see any way how to use it in combination with aggregation. What am I missing?

Answer 1

In Postgres 9.4 you could use json_build_object().

For your example, it works like:

SELECT group_id, 
       json_agg(json_build_object('id', id, 'name', name, 'body', body)) 
FROM temp
GROUP BY group_id;

this is a more friendly way, Postgres loves us :3