JSON_AGG

Aggregate values from grouped rows into a single JSON array.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

JSON_AGG(expr)

Description

## Overview Collects values from grouped rows and returns them as a single JSON array. Unlike most aggregate functions, JSON_AGG includes NULL input values as JSON null entries in the output rather than silently discarding them; this preserves row count and positional alignment with ORDER BY. Use JSON_AGG when you need to fold a detail set into a single JSON document per parent row (for example rolling up order lines per order, tags per post, or samples per series). For the binary-JSON variant with the same semantics, use JSONB_AGG. ## Behavior - Returns NULL for an empty group, not an empty array. Wrap with COALESCE(JSON_AGG(x), '[]'::JSON) when you need an empty array. - NULL input values are emitted as JSON null, not skipped. - Element order matches the group's input order unless an ORDER BY clause is supplied inside the aggregate. - The result type is JSON (text-based). For the binary variant use JSONB_AGG. - Parse failures cannot occur because the function builds JSON from typed values rather than parsing a string. - Works inside windowed and grouped contexts; combines with FILTER for conditional aggregation. ## Compatibility - Follows the SQL/JSON aggregate pattern from the SQL:2016 standard, with behavior aligned to widely used SQL implementations that expose json_agg. - JSON output conforms to RFC 8259.

Parameters

Name	Type	Description
`expr`		Specifies the expression to aggregate. One JSON element is appended per input row, including rows where the value is NULL. Accepts strings, numbers, booleans, JSON values, arrays, and composite types.

Examples

-- Aggregate string values into a JSON array
SELECT JSON_AGG(name) AS names
FROM (VALUES ('Alice'), ('Bob'), ('Carol')) AS t(name);

-- Aggregate integers from a realistic table
SELECT JSON_AGG(order_id ORDER BY order_id) AS order_ids
FROM ecommerce.sales.orders
WHERE order_date = DATE '2026-04-19';

-- NULL values are preserved as JSON null entries
SELECT JSON_AGG(v) AS arr
FROM (VALUES ('a'), (NULL), ('c')) AS t(v);

-- Group by a dimension to produce one array per key
SELECT department, JSON_AGG(employee_name ORDER BY employee_name) AS staff
FROM hr.core.employees
GROUP BY department;

-- Empty group produces NULL rather than []
SELECT JSON_AGG(name) AS arr
FROM (VALUES ('only_row')) AS t(name)
WHERE 1 = 0;

-- Combine with JSON_BUILD_OBJECT to materialize a list of records
SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name) ORDER BY id) AS users
FROM iam.directory.users;

Pitfalls

An empty group returns NULL, not '[]'. Downstream code that expects an array must COALESCE the result.
NULL inputs appear as JSON null entries, so the output array length equals the input row count; this differs from most aggregates, which skip NULL.
Without an explicit ORDER BY inside the aggregate, element order is unspecified and may vary between runs.
The result is JSON (text), not JSONB. Comparisons and containment checks are expensive on JSON; cast to JSONB or use JSONB_AGG when you need those operations.
Large groups can produce very large documents. A column limit on string output may truncate the result; watch for this when aggregating millions of rows per key.