Select columns inside json_agg

Question

I have a query like:

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;

How can I select the columns in b so I don't have b.item_id in the JSON object?

I have read about ROW, but it returns a JSON object like:

{"f1": "Foo", "f2": "Bar"}

I would need to remap the JSON object once it is fetched to match the proper column keys. I'd like to avoid that and keep original column names.

Answer 1

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:

• Return as array of JSON objects in SQL (Postgres)

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.

Answer 2

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.

Answer 3

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

Answer 4

You can use json_build_object like this

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;

Answer 5

I have found that it's best to create the JSON, then aggregate it. e.g.

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)

Note this can be done as a subquery if you don't like CTEs (or have performance problems because of using it).

Note also, if you're going to be doing this a lot, it may be beneficial to create a function to wrap the key-value pairs for you so the code looks cleaner. You would pass your function (for example) 'ecks', 'x' and it would return "ecks": "x".

Answer 6

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;

If you need to exclude multiple columns then

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;

Answer 7

While there's still no way to do anything about the select all columns but one bit, but you can use json_agg(to_json(b.col_1, b.col_2, b.col_3 ...)) to get a json array of jsons each in the format {"col_1":"col_1 value", ...}.

So the query would look something like:

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;

and would return rows as:

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

(I'm on Postgres 9.5.3 now and not 100% sure when this support was added.)