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

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 tempGROUP BY group_id;

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

You don't need a temp table or type for this, but it's not beautiful.

SELECT json_agg(row_to_json( (SELECT r FROM (SELECT id, name, body) r) )) FROM tGROUP BY group_id;

Here, we use two subqueries - first, to construct a result set with just the three desired columns, then the outer subquery to get it as a composite rowtype.

It'll still perform fine.

For this to be done with less ugly syntax, PostgreSQL would need to let you set aliases for anonymous rowtypes, like the following (invalid) syntax:

SELECT json_agg(row_to_json( ROW(id, name, body) AS (id, name, body) )) FROM tGROUP BY group_id;

or we'd need a variant of row_to_json that took column aliases, like the (again invalid):

SELECT json_agg(row_to_json( ROW(id, name, body), ARRAY['id', 'name', 'body'])) FROM tGROUP BY group_id;

either/both of which would be nice, but aren't currently supported.

Building on @Craig's answer to make it more elegant, here the composite rowtype is built in the from list

select json_agg(row_to_json(s))from    t    cross join lateral     (select id, name, body) sgroup by group_id;                                       json_agg                                       -------------------------------------------------------------------------------------- [{"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"}]