postgresql 如何使用 GROUP BY 子句将正确的属性名称设置为 json 聚合结果？

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

提问by Przemek

I have a table tempdefined like this:

我有一个这样temp定义的表：

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_idand aggregated to json. However, query like this:

我想生成一个按group_idjson分组并聚合到 json 的结果。但是，像这样查询：

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, f3instead of id, name, bodyas required. I know it is possible to alias them properly by using a subquery or a common table expression, for example like this:

在JSON对象的属性被命名为f1，f2，f3而不是id，name，body按要求。我知道可以通过使用子查询或公用表表达式来正确地给它们别名，例如这样：

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?

但老实说，我不知道如何将它与聚合结合使用。我错过了什么？

回答by Giordhano

In Postgres 9.4 you could use json_build_object().

在 Postgres 9.4 中，您可以使用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

这是一种更友好的方式，Postgres 爱我们：3

回答by Craig Ringer

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:

为了使用不那么难看的语法完成此操作，PostgreSQL 需要让您为匿名行类型设置别名，如下面（无效）语法：

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_jsonthat took column aliases, like the (again invalid):

或者我们需要一个带有row_to_json列别名的变体，例如（再次无效）：

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.

任何一个/两个都很好，但目前不支持。

回答by Clodoaldo Neto

Building on @Craig's answerto make it more elegant, here the composite rowtype is built in the fromlist

建立在@克雷格的答案，使其更加优雅，这里的复合行类型是建立在from列表

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

回答by Clodoaldo Neto

I was just going to post a very similar solution to yoursjust 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"}]

回答by AndreyT

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;

回答by Przemek

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