Menu

PostgreSQL jsonb_agg() Function

Updated on

The PostgreSQL jsonb_agg() aggregate collects input values into a JSON array and returns the jsonb 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.

jsonb_agg() Syntax

jsonb_agg(value [ORDER BY sort_expression])
  • value: A value or expression to convert to JSONB 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 JSONB array.

jsonb_agg() Example

This query creates one JSONB 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,
    jsonb_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 jsonb_agg() orders elements within each array. The final ORDER BY name sorts the result rows. If you need an empty JSONB array when there are no input rows, use COALESCE(jsonb_agg(value), '[]'::jsonb).

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

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