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

_{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:

• How to select sub-object with given keys from JSONB?

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:

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

• mlt: > or several keys Note that json(b)-text[] is available starting from 10.
• Luiz Fernando da Silva: The solution 3 worked for me like a charm!
• Ajay Takur: Alternative of function json_build_object() in postgres version 9.2.?

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.

• Erwin Brandstetter: This new features seems to be what the OP was hoping for. I added a link to my answer.
• fraxture: When I tried the subselect variant, I got an error related to the json_agg function: function json_agg(record) does not exist
• user1822: @fraxture: then you are not using Postgres 9.6
• fraxture: Indeed that was the problem. Is there any way to filter columns in v9.2?

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

• Inkling: This doesn't work (at least in recent versions); to_json() isn't variadic.