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?
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
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.
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) s
group 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"}]
If you need all fields from table, then you may use this approach:
SELECT
group_id, json_agg(temp.*)
FROM
temp
GROUP BY
group_id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With