Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 898
Przemek Avatar asked Jul 26 '14 11:07

Przemek


4 Answers

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

like image 142
Giordhano Avatar answered Oct 07 '22 12:10

Giordhano


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.

like image 29
Craig Ringer Avatar answered Oct 07 '22 12:10

Craig Ringer


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"}]
like image 35
Clodoaldo Neto Avatar answered Oct 07 '22 12:10

Clodoaldo Neto


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;
like image 38
AndreyT Avatar answered Oct 07 '22 11:10

AndreyT