Menu

PostgreSQL jsonb_object_agg() Function

Updated on

The PostgreSQL jsonb_object_agg() aggregate combines key-value rows into a value of type jsonb. It converts each key to text and each value to JSONB. A key cannot be SQL NULL; a NULL value is included as JSON null.

jsonb_object_agg() Syntax

jsonb_object_agg(key, value [ORDER BY sort_expression]) -> jsonb
  • key: A value to convert to a text object key. It must not be SQL NULL.
  • value: A value converted to JSONB. SQL NULL is included as JSON null.
  • ORDER BY sort_expression: Optional. Controls the order in which input rows are passed to the aggregate.

If there are no input rows, the aggregate returns SQL NULL, not an empty object.

jsonb_object_agg() Example

When multiple rows have the same key, jsonb_object_agg() keeps the last value it receives for that key. Use ORDER BY inside the aggregate if you want to define which value is last:

WITH statuses(key, value, seq) AS (
    VALUES
        ('state', 'pending'::text, 1),
        ('state', 'paid', 2),
        ('comment', NULL::text, 3)
)
SELECT jsonb_object_agg(key, value ORDER BY seq) AS status
FROM statuses;

The resulting object has "state": "paid" because that row comes last in the aggregate order, and "comment": null because SQL NULL is retained as a JSON null value.

Unlike json, the jsonb type does not preserve object key order and discards duplicate keys, keeping the last value. If duplicate keys should instead cause an error, PostgreSQL 18 provides jsonb_object_agg_unique(). See json_object_agg() for the text-preserving json variant.

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