array_agg(), string_agg(), jsonb_agg(), jsonb_object_agg()
The aggregate functions array_agg(), string_agg(), jsonb_agg(), and jsonb_object_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).
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
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.
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}