Aggregating arrays, strings, json and range
The aggregate functions array_agg(), jsonb_agg(), jsonb_object_agg(), string_agg(), and range_agg are described in this same section because each produces, as a single value, a list of the values that are aggregated. (The term "list" is used informally and somewhat loosely here. The examples make the sense in which the word is used clear.)
The notion "list" implies that the list members are ordered. (In contrast, "set" brings no such ordering notion.) For array_agg(), string_agg(), jsonb_agg(), and json_agg(), the list order is determined by the ORDER BY clause in the SELECT list expression that invokes the aggregate function. Because the single value produced by jsonb_object_agg() (and json_object_agg()) is a JSON object, and because the elements of such a value have no order, the ORDER BY clause in the SELECT list expression has no effect. (The elements are accessed by field name.)
This makes array_agg(), string_agg(), and jsonb_agg() unique among ordinary aggregate functions. Usually, as the example of sum() demonstrates, the order of the aggregated values is of no consequence. (The term "ordinary" aggregate function is used for the ones that are invoked with the GROUP BY syntax or the OVER syntax. The formal terms "within-group ordered-set" aggregate function and "within-group hypothetical-set" aggregate function are used for the "extraordinary" kinds.)
array_agg
Signature:
input value: anynonarray
anyarray
return value: anyarray
Purpose: Returns an array whose elements are the individual values that are aggregated. The to-be-aggregated values must be homogeneous: either all nonarray values; or all array values with the same dimensionality, as returned by array_ndims(), as each other. See also the account of array_agg() in the dedicated Array data types and functionality section.
The order of the resulting array elements (i.e. the mapping of element value to index value) is determined by the order defined by the GROUP BY or the OVER invocation syntax. When nonarray (i.e. scalar) values are aggregated, the result is a 1-dimensional array. When anyarray values with dimensionality N are aggregated, the result is an array with dimensionality (N + 1).
jsonb_agg
This aggregate function, together with json_agg(), are described fully in the jsonb_agg() section within the overall JSON section.
Signature:
input value: anyelement
return value: jsonb
Purpose: Returns a JSON array whose elements are the JSON values formed from each of the to-be-aggregated values.
jsonb_object_agg
This aggregate function, together with json_object_agg(), are described fully in the jsonb_object_agg() section within the overall JSON section.
Signature:
input value: "any", "any"
return value: jsonb
Purpose: Returns a JSON object whose fields are the JSON elements formed by each of the to-be-aggregated value pairs. The first value in the pair provides the name (key) of the field and the second value in the pair provides its value. Because the order of key-value pairs in a JSON object is undefined (values are addressed by their key), the aggregation order has no effect on the result.
string_agg
Signature:
input value: text, text
bytea, bytea
return value: text
bytea
Purpose: Returns a single value produced by concatenating the aggregated values (first argument) separated by a mandatory separator (second argument). The first overload has text inputs and returns text. The second overload has bytea inputs and returns bytea. (The PostgreSQL documentation describes the bytea data type in section 8.4. Binary Data Types.
Here's a basic example:
drop table if exists t cascade;
create table t(k int primary key, vt text not null, vb bytea not null);
insert into t(k, vt, vb) values
(1, 'm', 'm'::bytea),
(2, 'o', 'o'::bytea),
(3, 'u', 'u'::bytea),
(4, 's', 's'::bytea),
(5, 'e', 'e'::bytea);
select vt, vb from t order by k;
This is the result:
vt | vb
----+------
m | \x6d
o | \x6f
u | \x75
s | \x73
e | \x65
Now try this:
with a as (
select
string_agg(vt, null order by k) as text_agg,
string_agg(vb, '.'::bytea order by k) as bytea_agg
from t)
select
text_agg,
bytea_agg,
convert_from(bytea_agg, 'utf-8') as bytea_agg_text
from a;
This is the result:
text_agg | bytea_agg | bytea_agg_text
----------+----------------------+----------------
mouse | \x6d2e6f2e752e732e65 | m.o.u.s.e
range_agg
The range_agg function is used to aggregate a set of ranges into a single range or an array of ranges. It's particularly useful for summarizing sequences of continuous or discrete values, such as timestamps or numerical data. It helps condense multiple ranges into a more concise representation. For example, if you have several time intervals, range_agg can combine overlapping or adjacent intervals into larger ones.
If the input ranges are contiguous or overlapping, range_agg can merge them into a single encompassing range. If there are gaps or overlaps that are not allowed, it will raise an error. If the input ranges have gaps or overlaps that you want to preserve, range_agg can return an array of ranges. You can control whether gaps and overlaps are permitted using optional parameters.
input value: anyrange
anymultirange
return value: anymultirange
For example:
DROP TABLE IF EXISTS event_times CASCADE;
CREATE TABLE event_times (
event_id INT,
time_range tsrange
);
INSERT INTO event_times (event_id, time_range) VALUES
(1, '[2025-01-01 09:00, 2025-01-01 10:00)'::tsrange),
(2, '[2025-01-01 10:00, 2025-01-01 11:00)'::tsrange),
(3, '[2025-01-01 12:00, 2025-01-01 13:00)'::tsrange),
(4, '[2025-01-01 14:00, 2025-01-01 15:00)'::tsrange),
(5, '[2025-01-01 14:30, 2025-01-01 15:30)'::tsrange);
-- Note Event 5 overlaps with event 4
To combine 2 ranges, you can use range_agg as,
SELECT range_agg(time_range)
FROM event_times
WHERE event_id IN (1, 2);
This would create a combined range as,
range_agg
-------------------------------------------------
{["2025-01-01 09:00:00","2025-01-01 11:00:00")}
You can control the order of ranges within the resulting array using ORDER BY.
SELECT range_agg(time_range ORDER BY event_id DESC)
FROM event_times
WHERE event_id IN (1, 3);
Now the ranges will be ordered by event_id.
range_agg
-----------------------------------------------------------------------------------------------
{["2025-01-01 09:00:00","2025-01-01 10:00:00"),["2025-01-01 12:00:00","2025-01-01 13:00:00")}
Examples
Each of these aggregate functions is invoked by using the same syntax—either the GROUP BY syntax or the OVER syntax. First create and populate the test table:
drop table if exists t cascade;
create table t(
k int primary key,
class int not null,
v text not null);
insert into t(k, class, v)
select
(1 + s.v),
case (s.v) < 3
when true then 1
else 2
end,
chr(97 + s.v)
from generate_series(0, 5) as s(v);
select k, class, v from t order by k;
This is the result:
k | class | v
---+-------+---
1 | 1 | a
2 | 1 | b
3 | 1 | c
4 | 2 | d
5 | 2 | e
6 | 2 | f
GROUP BY syntax
Try this:
select
class,
array_agg(v order by k desc) filter (where v <> 'b') as "array_agg(v)",
string_agg(v, ' ~ ' order by k desc) filter (where v <> 'e') as "string_agg(v)",
jsonb_agg(v order by v desc) filter (where v <> 'b') as "jsonb_agg",
jsonb_object_agg(v, k order by v desc) filter (where v <> 'e') as "jsonb_object_agg(v, k)"
from t
group by class
order by class;
This is the result:
class | array_agg(v) | string_agg(v) | jsonb_agg | jsonb_object_agg(v, k)
-------+--------------+---------------+-----------------+--------------------------
1 | {c,a} | c ~ b ~ a | ["c", "a"] | {"a": 1, "b": 2, "c": 3}
2 | {f,e,d} | f ~ d | ["f", "e", "d"] | {"d": 4, "f": 6}
As promised, the result produced by jsonb_object_agg() is insensitive to order.
OVER syntax
Try this:
select
class,
(array_agg(v) filter (where v <> 'b') over w) as "array_agg(v)",
(string_agg(v, ' ~ ') filter (where v <> 'b') over w) as "string_agg(v)",
(jsonb_agg(v) filter (where v <> 'b') over w) as "string_agg(v)",
(jsonb_object_agg(v, k) filter (where v <> 'e') over w) as "jsonb_object_agg(v, k)"
from t
window w as (
partition by class
order by k desc
range between unbounded preceding and current row)
order by 1;
This is the result:
class | array_agg(v) | string_agg(v) | string_agg(v) | jsonb_object_agg(v, k)
-------+--------------+---------------+-----------------+--------------------------
1 | {c} | c | ["c"] | {"c": 3}
1 | {c} | c | ["c"] | {"b": 2, "c": 3}
1 | {c,a} | c ~ a | ["c", "a"] | {"a": 1, "b": 2, "c": 3}
2 | {f} | f | ["f"] | {"f": 6}
2 | {f,e} | f ~ e | ["f", "e"] | {"f": 6}
2 | {f,e,d} | f ~ e ~ d | ["f", "e", "d"] | {"d": 4, "f": 6}