ARRAY_AGG

Reference material for ARRAY_AGG function

Concatenates input values, including NULL values, into an array.

Syntax

ARRAY_AGG(<expression>) [FILTER ([WHERE] <condition>)]

Parameters

Parameter	Description	Supported input type
<expression>	Expression of any type to be accumulated into an array.	Any
<condition>	An optional boolean expression to filter rows used in aggregation	BOOL

Return Type

ARRAY of the same type as the input data. If there is no input data, ARRAY_AGG returns NULL.

Example

The following examples use a player_information table:

SELECT nickname, playerid
FROM (VALUES
    ('stephen70', 1),
    ('burchdenise', 7),
    ('sabrina21', NULL)
) AS player_information(nickname, playerid);

nickname text	playerid int null
stephen70	1
burchdenise	7
sabrina21	NULL

Rows: 3Execution time: 5ms

The following example aggregates the columns into arrays, including NULL values:

SELECT
    ARRAY_AGG(nickname) AS nicknames,
    ARRAY_AGG(playerid) AS playerids
FROM
    (VALUES
        ('stephen70', 1),
        ('burchdenise', 7),
        ('sabrina21', NULL)
    ) AS t(nickname, playerid);

nicknames array(text) null	playerids array(int null) null
[‘stephen70’, ‘burchdenise’, ‘sabrina21’]	[1, 7, NULL]

Rows: 1Execution time: 10.40ms

The following example shows that when a WHERE filter rejects all rows, ARRAY_AGG returns NULL:

SELECT
    ARRAY_AGG(nickname) AS nicknames,
    ARRAY_AGG(playerid) AS playerids
FROM
    (VALUES
        ('stephen70', 1),
        ('burchdenise', 7),
        ('sabrina21', NULL)
    ) AS t(nickname, playerid)
WHERE
    playerid = 42;

nicknames array(text) null	playerids array(int) null
NULL	NULL

Rows: 1Execution time: 8.85ms