JSONB_AGG

Aggregate values from grouped rows into a single JSONB array.

Category: jsonReturns: JSONBDialect: PostgreSql

Syntax

JSONB_AGG(expr)

Description

## Overview Aggregates input values from grouped rows into a JSONB array. Behaves like JSON_AGG but produces a binary-JSON result that supports indexing, containment operators, and efficient nested access. NULL input values are preserved as JSONB null entries so the output length always equals the input row count. Use JSONB_AGG when you need the output to participate in containment checks (@>), key existence tests (?), or path lookups (-> / ->>), or when the result will be repeatedly queried. For text-only use cases, JSON_AGG is cheaper to construct. ## Behavior - Returns NULL for an empty group; wrap with COALESCE(..., '[]'::JSONB) to obtain an empty array. - NULL inputs appear as JSONB null entries. - Order of elements follows the aggregate ORDER BY clause when provided; otherwise it is unspecified. - Result type is JSONB (binary). Use JSON_AGG for the text-based variant. - Duplicate object keys inside aggregated objects are deduplicated by JSONB's canonical form (last-write-wins). - Supports FILTER (WHERE ...) for conditional aggregation. ## Compatibility - Follows the SQL/JSON aggregate pattern from SQL:2016. - Output conforms to RFC 8259 when serialized back to text.

Parameters

Name	Type	Description
`expr`		Specifies the expression to aggregate. One JSONB element is appended per input row; NULL input values are emitted as JSONB null. Accepts strings, numbers, booleans, JSONB, arrays, and composite types.

Examples

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

-- Aggregate from a realistic table with ORDER BY
SELECT product_category, JSONB_AGG(product_name ORDER BY product_name) AS products
FROM ecommerce.catalog.products
GROUP BY product_category;

-- NULL values are preserved
SELECT JSONB_AGG(v) AS arr
FROM (VALUES ('x'), (NULL), ('z')) AS t(v);

-- Empty group returns NULL, not []
SELECT COALESCE(JSONB_AGG(product_name), '[]'::JSONB) AS products
FROM ecommerce.catalog.products
WHERE product_category = 'nonexistent';

-- Build per-group structured JSONB documents
SELECT customer_id,
       JSONB_AGG(JSONB_BUILD_OBJECT('order_id', order_id, 'total', total) ORDER BY order_id) AS orders
FROM ecommerce.sales.orders
GROUP BY customer_id;

Pitfalls

Empty group returns NULL, not '[]'. COALESCE is required when downstream code expects an array.
Duplicate object keys inside aggregated objects are deduplicated by JSONB canonicalization (last wins), which silently changes the output compared with JSON_AGG.
Without ORDER BY inside the aggregate, element order is unspecified and can vary between runs.
Building very large JSONB documents is memory-intensive; consider batching or pushing aggregation into a materialized view.
NULL inputs appear as JSONB null entries, not skipped. The output array length equals the input row count.