Menu

PostgreSQL json_agg() Function

Updated on

The PostgreSQL json_agg() aggregate collects input values into a JSON array and returns the json type. SQL NULL inputs are included as JSON null values. Array element order is not guaranteed unless you specify an order inside the aggregate call.

json_agg() Syntax

json_agg(value [ORDER BY sort_expression])
  • value: A value or expression to convert to JSON and add to the array.
  • ORDER BY sort_expression: Optional. Controls the order of elements in the result array.

If the aggregate receives no rows, it returns SQL NULL, not an empty JSON array.

json_agg() Example

This query creates one JSON array of hobbies for each person. The NULL hobby appears as JSON null:

WITH hobbies(name, hobby, sort_order) AS (
    VALUES
        ('Tim', 'Football', 1),
        ('Tim', NULL::text, 2),
        ('Tim', 'Baseball', 3),
        ('Tom', 'Piano', 1),
        ('Tom', 'Violin', 2)
)
SELECT
    name,
    json_agg(hobby ORDER BY sort_order) AS hobbies
FROM hobbies
GROUP BY name
ORDER BY name;

Tim’s array contains the JSON values "Football", null, and "Baseball", in that order. Tom’s array contains "Piano" followed by "Violin".

The ORDER BY inside json_agg() orders elements within each array. The final ORDER BY name sorts the result rows. If you need an empty JSON array when there are no input rows, use COALESCE(json_agg(value), '[]'::json).

Compare with jsonb_agg(), which returns jsonb rather than json.

For more details, see PostgreSQL’s official aggregate function documentation.