Selecione colunas dentro de json_agg

Infelizmente, não há provisão na sintaxe SQL para dizer "todas as colunas, exceto esta coluna" . Você pode atingir seu objetivo soletrando a lista restante de colunas em uma expressão do tipo linha :

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;

Isso é abreviação para a forma mais explícita: .ROW(b.col1, b.col2, b.col3)

No entanto, os nomes das colunas não são preservados em expressões de tipo de linha. Você obtém nomes de chave genéricos no objeto JSON dessa maneira. Vejo 3 opções para preservar os nomes das colunas originais:

1. Transmitir para o tipo registrado

Transmitir para um tipo de linha conhecido (registrado). Um tipo é registrado para cada tabela ou exibição existente ou com uma CREATE TYPEinstrução explícita. Você pode usar uma tabela temporária para uma solução ad-hoc (duração da sessão):

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. Use uma subseleção

Use uma subseleção para construir uma tabela derivada e fazer referência à tabela como um todo . Isso também carrega nomes de coluna. É mais detalhado, mas você não precisa de um tipo registrado:

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;

3. json_build_object()no Postgres 9.4 ou posterior

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

Relacionado:

• Retornar como array de objetos JSON no SQL (Postgres)

Semelhante para jsonbcom as respectivas funções jsonb_agg()e jsonb_build_object().

Para o Postgres 9.5 ou posterior, veja também a resposta de a_horse com uma nova variante de sintaxe mais curta: Postgres adicionou o operador menos -parajsonb dizer "todas as chaves exceto esta chave" .
Já o Postgres 10 "exceto várias chaves" é implementado com o mesmo operador tomando text[]como 2º operando - como o mlt comentado.

A partir do 9.6, você pode simplesmente usar -para remover uma chave de um JSONB:

SELECT a.id, a.name, jsonb_agg(to_jsonb(b) - 'item_id') as "item"
FROM a
  JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

to_jsonb(b)converterá a linha inteira e - 'item_id'removerá a chave com o nome item_id, o resultado disso será agregado.

Você pode realmente fazer isso sem agrupar, usando subconsultas

SELECT 
  a.id, a.name, 
  ( 
    SELECT json_agg(item)
    FROM (
      SELECT b.c1 AS x, b.c2 AS y 
      FROM b WHERE b.item_id = a.id
    ) item
  ) AS items
FROM a;

retorna

{
  id: 1,
  name: "thing one",
  items:[
    { x: "child1", y: "child1 col2"},
    { x: "child2", y: "child2 col2"}
  ]
}

Este artigo de John Atten é muito interessante e tem mais detalhes

Você pode usar json_build_objectassim

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;

Descobri que é melhor criar o JSON e agregá-lo. por exemplo

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)

Observe que isso pode ser feito como uma subconsulta se você não gostar de CTEs (ou tiver problemas de desempenho por causa do uso).

Observe também que, se você for fazer muito isso, pode ser benéfico criar uma função para agrupar os pares de valores-chave para você, para que o código pareça mais limpo. Você passaria sua função (por exemplo) 'ecks', 'x'e ela retornaria "ecks": "x".

SELECT
  a.id,
  a.name,
  jsonb_agg(row_to_json(b.*)::jsonb - 'item_id') as item
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

Se você precisar excluir várias colunas, então

SELECT
  a.id,
  a.name,
  jsonb_agg(row_to_json(b.*)::jsonb - '{item_id,other_col}'::text[]) as item
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

Embora ainda não haja como fazer nada sobre selecionar todas as colunas, exceto um bit, mas você pode usar json_agg(to_json(b.col_1, b.col_2, b.col_3 ...))para obter uma matriz json de jsons cada um no formato {"col_1":"col_1 value", ...}.

Assim, a consulta seria algo como:

SELECT a.id, a.name, json_agg(to_json(b.col_1,b.col_2,b.col_3...)) as item
  FROM a
  JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

e retornaria linhas como:

id, name, item
8, the_name, [{"col_1":"value_1","col_2":"value_2","col_3":"value_3"...}, {"col_1":"value_1.2","col_2":"value_2.2","col_3":"value_3.2"...},...]
9, the_next_name, [{"col_1":"value_1.3","col_2":"value_2.3","col_3":"value_3.3"...},   {"col_1":"value_1.4","col_2":"value_2.4","col_3":"value_3.4"...},...]
...

(Estou no Postgres 9.5.3 agora e não tenho 100% de certeza de quando esse suporte foi adicionado.)