Select columns inside json_agg

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;

3. json_build_object() in Postgres 9.4 or later

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.


Starting with 9.6 you can simply use - to remove a key from a 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) will convert the whole row and - 'item_id' will then remove the key with the name item_id the result of that is then aggregated.


You can actually do it without group by, using subqueries

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;

returns

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

This article from John Atten is really interesting and has more details

Tags:

Postgresql

Json

Group By

Row

Recent Posts