59

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

110

Assuming a.id is the PRIMARY KEY, so it covers all columns of table a in GROUP BY.

There is no provision in SQL syntax to say "all columns except this one".

Postprocessing

But since Postgres 9.5 we can say "all keys except this one" for jsonb objects with the minus operator - taking text as 2nd operand. After converting the row to jsonb, but before the aggregation. We do need jsonb instead of json!

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;

Since Postgres 10 "except several keys" is implemented with the - operator taking text[]:

SELECT a.id, a.name
     , jsonb_agg(to_jsonb(b) - '{item_id, col2}'::text[]) AS item
FROM   a JOIN b ON b.item_id = a.id
GROUP  BY a.id;

fiddle

Note the explicit cast ::text[].
(We could use json_agg() instead of jsonb_agg() to output the type json, but I don't see the point after converting to jsonb.)

Related:

Preprocessing

Or we can achieve your goal by spelling out the remaining list of columns in a row-type expression. Before converting to JSON:

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

That's short for the more explicit form: ROW(b.id, b.col1, b.col2).

However, columns names are not preserved in a row-type expression. We get generic key names in the JSON object. That's what you observed in your attempt.
I see 3 options to preserve original column names:

1. Cast to registered type

Cast to a registered row type. A type is registered implicitly for every existing table(-like object) or with an explicit CREATE TYPE statement. We might create a temporary type as ad-hoc solution for the current session:

CREATE TYPE pg_temp.tmp_x AS (id int, col1 int, col2 text);  -- adequate data types!

Then:

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

2. Use a subselect

Use a subselect to construct a derived table and reference that as a whole. This also carries column names. More verbose, but we don't need a registered type:

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

3. json_build_object() in Postgres 9.4 or later

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

fiddle

All the same for jsonb with the respective functions jsonb_agg() and jsonb_build_object().

Related:

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

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. Commented 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
    Commented Aug 13, 2018 at 0:01
  • @fraxture: then you are not using Postgres 9.6
    – user1822
    Commented Aug 13, 2018 at 10:06
  • Indeed that was the problem. Is there any way to filter columns in v9.2?
    – fraxture
    Commented Aug 13, 2018 at 13:51
14

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

7

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

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

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
    Commented Oct 28, 2020 at 6:01

Your Answer

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

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