Postgres 10+
Convert the whole row to a jsonb object and eliminate a single key (pg 9.5+) or an array of keys (pg 10+) with the - operator before aggregating:
SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;
The explicit cast in '{id, val2}'::text[] is necessary to disambiguate from the overloaded function taking a single key as text.
See:
- Select columns inside jsonb_agg
Postgres 9.4+
jsonb_build_object() or json_build_object().
SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl
GROUP BY val2;
The manual:
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
Postgres 9.3+
to_jsonb() (or to_json) with a ROW expression does the trick. (Or row_to_json() with optional line feeds):
SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34
FROM tbl
GROUP BY val2;
But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)
CREATE TYPE foo AS (val3 text, val4 text); -- once in the same session
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34
FROM tbl
GROUP BY val2;
Or use a subselect instead of the ROW expression. More verbose, but without type cast:
SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34
FROM tbl
GROUP BY val2;
to_jsonb() is an optional addition to add (insignificant) line breaks in the JSON document.
More in Craig's related answer:
- Using row_to_json() with nested joins
fiddle
Old sqlfiddle
to_json with array_agg with composite type
begin;
create table mytable(
id bigint, value_two text, value_three text, value_four text);
insert into mytable(id,value_two, value_three,value_four)
values
( 1, 'a', 'A', 'AA'),
(2, 'a' , 'A2' , 'AA2'),
(3, 'b' , 'A3', 'AA3'),
( 4, 'a' , 'A4', 'AA4'),
(5, 'b' , 'A5', 'AA5');
commit;
create type mytable_type as (value_three text, value_four text);
select value_two,
to_json( array_agg(row(value_three,value_four)::mytable_type))
from mytable
group by 1;