Przemek Przemek - 3 months ago 9
JSON Question

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

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

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 t
GROUP 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 t
GROUP 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 t
GROUP BY group_id;

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