在 SQL (Postgres) 中作为 JSON 对象数组返回

Return as array of JSON objects in SQL (Postgres)

提问by ehmicky

I have the following table MyTable:

我有下表MyTable：

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

I want to query an array of objects { value_three, value_four }grouped by value_two. value_twoshould be present on its own in the result. The result should look like this:

我想查询{ value_three, value_four }由value_two. value_two应该单独出现在结果中。结果应如下所示：

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

It does not matter whether it uses json_agg()or array_agg().

它是否使用json_agg()或都没有关系array_agg()。

However the best I can do is:

但是我能做的最好的是：

with MyCTE as ( select value_two, value_three, value_four from MyTable ) 
select value_two, json_agg(row_to_json(MyCTE)) value_four 
from MyCTE 
group by value_two;

Which returns:

返回：

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]

With an extra value_twokey in the objects, which I would like to get rid of. Which SQL (Postgres) query should I use?

value_two在对象中有一个额外的键，我想摆脱它。我应该使用哪个 SQL (Postgres) 查询？

回答by Erwin Brandstetter

json_build_object()in Postgres 9.4 or newer

json_build_object()在 Postgres 9.4 或更新版本中

SELECT value_two, json_agg(json_build_object('value_three', value_three
                                           , 'value_four' , value_four)) AS value_four
FROM   mytable 
GROUP  BY value_two;

The manual:

手册：

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.

从可变参数列表中构建 JSON 对象。按照惯例，参数列表由交替的键和值组成。

For any version (incl. Postgres 9.3)

对于任何版本（包括 Postgres 9.3）

row_to_json()with a ROWexpression would do the trick:

row_to_json()使用ROW表达式可以解决问题：

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   mytable
GROUP  BY value_two;

But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)

但是您会丢失原始列名。转换为已注册的行类型可以避免这种情况。（临时表的行类型也用于即席查询。）

CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two;

Or use a subselectinstead of the ROWexpression. More verbose, but without type cast:

或者使用子选择而不是ROW表达式。更详细，但没有类型转换：

SELECT value_two
     , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
FROM   mytable
GROUP  BY value_two;

More explanation in Craig's related answer:

克雷格的相关答案中有更多解释：

• PostgreSQL 9.2 row_to_json() with nested joins

• 带有嵌套连接的 PostgreSQL 9.2 row_to_json()

db<>fiddle here
Old SQL fiddle.

db<>fiddle here
旧的 SQL 小提琴。