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?

score 47 · Answer 1 · answered Jan 22 '16 at 03:21

47

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

answered Jan 22 '16 at 03:21

Giordhano

581
5
5

unfortunately there is no way to use DISTINCT with json_build_object – jonathancardoso Apr 21 '20 at 13:41
1

also, you cannot go beyond 100 arguments, so this only works up to 50 columns – svenema Apr 06 '21 at 14:05

Craig Ringer · Accepted Answer · 2014-07-26T11:56:53.897

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.

Not really elegant but looks fairly simple, in fact too simple for me to even consider this in the first place, thanks! — Przemek, Jul 26 '14 at 12:01

score 6 · Answer 3 · edited May 23 '17 at 11:54

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"}]

score 2 · Answer 4 · edited May 23 '17 at 12:25

I was just going to post a very similar solution to yours just using a temporary table

create table t (
    id int,
    name text,
    body text,
    group_id int
);
insert into t (id, name, body, group_id) values
(1, 'test_1', 'body_1', 1),
(2, 'test_2', 'body_2', 1),
(3, 'test_3', 'body_3', 2),
(4, 'test_4', 'body_4', 2);

create temporary table tt(
    id int,
    name text,
    body text
);

select group_id, json_agg(row(id, name, body)::tt)
from t
group by group_id;
 group_id |                  json_agg                   
----------+---------------------------------------------
        1 | [{"id":1,"name":"test_1","body":"body_1"}, +
          |  {"id":2,"name":"test_2","body":"body_2"}]
        2 | [{"id":3,"name":"test_3","body":"body_3"}, +
          |  {"id":4,"name":"test_4","body":"body_4"}]

score 2 · Answer 5 · answered Aug 24 '16 at 15:04

2

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;

answered Aug 24 '16 at 15:04

AndreyT

1,449
1
15
28

score 0 · Answer 6 · answered Jul 26 '14 at 11:24

Well, answering my own question a couple of minutes after asking but I have found a way... I just don't know it's the best one. I solved it by creating a composite type:

CREATE TYPE temp_type AS (
  id bigint,
  name text,
  body text
);

And rewriting my query by adding a cast to the type:

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

Which produced the expected result:

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

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

6 Answers6

Linked

Related