如何使用GROUP BY子句为JSON聚合结果设置正确的属性名称? [英] How to set correct attribute names to a json aggregated result with GROUP BY clause?

问题描述

我有一个表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

我想产生按group_id分组并汇总到json的结果.但是，这样查询:

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;

产生此结果:

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

根据需要，json对象中的属性被命名为f1，f2，f3，而不是id，name，body.我知道可以通过使用子查询或公用表表达式对它们进行适当的别名，例如:

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;

哪个会产生以下结果:

[{"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?

推荐答案

您不需要临时表或类型，但这并不漂亮.

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.

要使用不太丑陋的语法来完成此操作，PostgreSQL将需要让您为匿名行类型设置别名，例如以下(无效)语法:

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;

或者我们需要row_to_json的变体，该变体具有列别名，例如(再次无效):

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.

这篇关于如何使用GROUP BY子句为JSON聚合结果设置正确的属性名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！