作为SQL中的JSON对象数组返回（Postgres）

Question

我有下表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

我想查询{ 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"}]

使用json_agg()还是都没有关系array_agg()。

但是，我能做的最好的事情是：

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;

哪个返回：

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

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

Answer 1

[json_build_object()](https://www.postgresql.org/docs/current/functions-

json.html#FUNCTIONS-JSON-CREATION-TABLE) 在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;

手册：

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

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

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;

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

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;

或使用子 选择 代替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;

Craig相关答案中的更多解释：

在这里
_拨弄

旧的SQL拨弄。