Return multiple columns of the same row as JSON array of objects

by

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:

db<>fiddle here
Old sqlfiddle

More Related Contents:

  1. Selecting data into a Postgres array
  2. Multiple array_agg() calls in a single query
  3. PostgreSQL unnest() with element number
  4. Aggregate columns with additional (distinct) filters
  5. How do I query using fields inside the new PostgreSQL JSON datatype?
  6. PostgreSQL: running count of rows for a query ‘by minute’
  7. Check if value exists in Postgres array
  8. Two SQL LEFT JOINS produce incorrect result
  9. Index for finding an element in a JSON array
  10. Query for array elements inside JSON type
  11. How can I import a JSON file into PostgreSQL?
  12. Create a pivot table with PostgreSQL
  13. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  14. Normalize array subscripts so they start with 1
  15. How to include “zero” / “0” results in COUNT aggregate?
  16. GROUP BY + CASE statement
  17. Is there something like a zip() function in PostgreSQL that combines two arrays?
  18. Best way to count rows by arbitrary time intervals
  19. Compute percents from SUM() in the same SELECT sql query
  20. Get the distinct sum of a joined table column
  21. Query for element of array in JSON column
  22. Aggregate a single column in query with many columns
  23. PostgreSQL JOIN with array type with array elements order, how to implement?
  24. How to store array or multiple values in one column
  25. Postgres window function and group by exception
  26. Count cumulative total in Postgresql
  27. PostgreSQL Index on JSON
  28. Check if NULL exists in Postgres array
  29. Operator does not exist: json = json
  30. Truncate timestamp to arbitrary intervals

Leave a Comment