Return as array of JSON objects in SQL (Postgres)
Solution 1:
json_build_object()
in Postgres 9.4 or newer
Or jsonb_build_object()
to return jsonb
.
SELECT value_two, json_agg(json_build_object('value_three', value_three
, 'value_four' , value_four)) AS value_four
FROM mytable
GROUP BY value_two;
The manual:
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
For any version (incl. Postgres 9.3)
row_to_json()
with a ROW
expression would do the trick:
SELECT value_two
, json_agg(row_to_json((value_three, value_four))) AS value_four
FROM mytable
GROUP BY value_two;
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 (value_three text, value_four text); -- once in the same session
SELECT value_two
, json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM mytable
GROUP BY value_two;
Or use a subselect instead of the ROW
expression. More verbose, but without type cast:
SELECT value_two
, json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
FROM mytable
GROUP BY value_two;
More explanation in Craig's related answer:
- PostgreSQL 9.2 row_to_json() with nested joins
db<>fiddle here
Old sqlfiddle