Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression:
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;
That's short for the more explicit form: ROW
(b.col1, b.col2, b.col3)
.
However, columns names are not preserved in row-type expressions. You get generic key names in the JSON object this way. I see 3 options to preserve original column names:
1. Cast to registered type
Cast to a well-known (registered) row type. A type is registered for every existing table or view or with an explicit CREATE TYPE
statement. You might use a temporary table for an ad-hoc solution (lives for the duration of the session):
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 a subselect
Use a subselect to construct a derived table and reference the table as a whole. This also carries column names. It is more verbose, but you don't need a registered type:
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;
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;
Related:
Similar for jsonb
with the respective functions jsonb_agg()
and jsonb_build_object()
.
For Postgres 9.5 or later also see a_horse's answer with a new shorter syntax variant: Postgres added the minus operator -
for jsonb
to say "all keys except this one key".
Since Postgres 10 "except several keys" is implemented with the same operator taking text[]
as 2nd operand - like mlt commented.