Menu

PostgreSQL json_object_agg() Function

Updated on

The PostgreSQL json_object_agg() aggregate combines key-value rows into a value of type json. It converts each key to text and converts each value to JSON. A key cannot be SQL NULL; a value can be NULL, in which case the object contains JSON null.

json_object_agg() Syntax

json_object_agg(key, value [ORDER BY sort_expression]) -> json
  • key: A value to convert to a text object key. It must not be SQL NULL.
  • value: A value converted to JSON. 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.

json_object_agg() Example

This query groups each person’s grades into a JSON object. The subject becomes the key, and the grade becomes the value:

WITH grades(name, subject, grade, subject_order) AS (
    VALUES
        ('Tim', 'Math', 'A'::text, 1),
        ('Tim', 'English', 'B', 2),
        ('Tim', 'Science', NULL::text, 3),
        ('Tom', 'Math', 'B', 1),
        ('Tom', 'English', 'A', 2)
)
SELECT
    name,
    json_object_agg(subject, grade ORDER BY subject_order) AS grades
FROM grades
GROUP BY name
ORDER BY name;

For Tim, the result contains the pairs "Math": "A", "English": "B", and "Science": null. The SQL NULL grade is kept as a JSON null value.

The json type preserves the text representation of an object, including duplicate keys and key order. If an input key appears more than once, consider PostgreSQL 18’s json_object_agg_unique() when duplicate keys should cause an error. For a binary representation that removes duplicate keys and keeps the last value, see jsonb_object_agg().

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