56

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.

0

7 Answers 7

102

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.

3
  • 1
    > or several keys Note that json(b)-text[] is available starting from 10.
    – mlt
    Aug 7, 2018 at 21:25
  • The solution 3 worked for me like a charm! Aug 20, 2019 at 18:11
  • Alternative of function json_build_object() in postgres version 9.2.?
    – Ajay Takur
    Sep 2, 2020 at 12:10
29

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.

4
  • 3
    This new features seems to be what the OP was hoping for. I added a link to my answer. Mar 14, 2018 at 15:06
  • When I tried the subselect variant, I got an error related to the json_agg function: function json_agg(record) does not exist
    – fraxture
    Aug 13, 2018 at 0:01
  • @fraxture: then you are not using Postgres 9.6
    – user1822
    Aug 13, 2018 at 10:06
  • Indeed that was the problem. Is there any way to filter columns in v9.2?
    – fraxture
    Aug 13, 2018 at 13:51
13

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

6

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;
2

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".

0
2
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;
1

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.)

1
  • This doesn't work (at least in recent versions); to_json() isn't variadic.
    – Inkling
    Oct 28, 2020 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.