PostgreSQL: Convert Multiple Rows to JSON

PostgreSQL provides powerful JSON functions that allow you to convert query results into JSON format. This is especially useful for APIs and reporting.

Using json_agg()

SELECT json_agg(t)
FROM (
  SELECT id, name, role
  FROM employee
) t;

This query converts multiple rows into a single JSON array.

Output Example

[
  {"id":1,"name":"Alice","role":"Developer"},
  {"id":2,"name":"Bob","role":"Manager"}
]

The json_agg() function aggregates rows into JSON arrays efficiently.