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