选择json_agg内的列

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

#1 楼

不幸的是，SQL语法中没有规定说“除这一列外的所有列”。您可以通过在行类型表达式中拼写剩余的列列表来实现目标：

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

这是更明确的形式的缩写：ROW (b.col1, b.col2, b.col3)。保存在行类型表达式中。您可以通过这种方式在JSON对象中获得通用键名称。我看到3个保留原始列名的选项：
1。强制转换为注册类型
强制转换为已知（注册）行类型。为每个现有表或视图或使用显式CREATE TYPE语句注册类型。您可以将临时表用于临时解决方案（在会话期间有效）：

CREATE TEMP TABLE x (col1 int, col2 text, col3 date);  -- use adequate data types!

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)::x) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

2。使用子选择
使用子选择来构造派生表并整体引用该表。这也带有列名。它比较冗长，但是您不需要注册类型：

SELECT a.id, a.name
     , json_agg((SELECT x FROM (SELECT b.col1, b.col2, b.col3) AS x)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

json_build_object()
相关信息：

以SQL（Postgres）中的JSON对象数组形式返回函数jsonb和jsonb_agg()。
对于Postgres 9.5或更高版本，还请参见a_horse的答案，其中包含一个新的较短语法变体：Postgres为jsonb_build_object()添加了负运算符-表示“除此键之外的所有键”。使用相同的运算符将jsonb作为第二个操作数来实现10个“除几个键外”-像mlt一样。

#2 楼

从9.6开始，您可以简单地使用-从JSONB中删除密钥：然后将其结果汇总。

#3 楼

实际上，您可以使用子查询而无需分组来实现

出席真的很有趣，并且有更多详细信息

#4 楼

您可以像这样使用json_build_object

SELECT 
  a.id, 
  a.name,
  json_agg(json_build_object('col1', b.col1, 'col2', b.col2) AS item
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

#5 楼

我发现最好创建JSON，然后对其进行聚合。例如，

with base as (
select a, b, ('{"ecks":"' || to_json(x) || '","wai":"' || to_json(y) || '","zee":"' || to_json(z) || '"}"')::json c
) select (a, b, array_to_json(array_agg(c)) as c)

注意，如果您不喜欢CTE（或者由于使用CTE而导致性能问题），则可以将其作为子查询来完成。

还要注意，如果您打算做很多事情，创建一个函数来包装键-值对可能会比较有益，这样代码看起来会更干净。您将传递函数（例如）'ecks', 'x'，并返回"ecks": "x"。

#6 楼

尽管除了选择一位之外，仍然没有办法对所有列进行任何选择，但是您可以使用json_agg(to_json(b.col_1, b.col_2, b.col_3 ...))以{"col_1":"col_1 value", ...}的格式获取json的json数组。

，因此查询将类似于：
/>
（我现在使用的是Postgres 9.5.3，不确定何时添加此支持。）