SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='array-agg'
ARRAY_AGG
Definition
The ARRAY_AGG
function in BigQuery creates an ARRAY
from another expression or table. It is basically the opposite of UNNEST
.
Optional Clauses
The clauses are applied in the following order:
Loading code...
OVER
: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses withinARRAY_AGG()
.DISTINCT
: Each distinct value ofexpression
is aggregated only once into the result.IGNORE NULLS
orRESPECT NULLS
: IfIGNORE NULLS
is specified, theNULL
values are excluded from the result. IfRESPECT NULLS
is specified or if neither is specified, theNULL
values are included in the result. An error is raised if an array in the final query result contains aNULL
element.ORDER BY
: Specifies the order of the values.- For each sort key, the default sort direction is
ASC
. - NULLs: In the context of the
ORDER BY
clause, NULLs are the minimum possible value; that is, NULLs appear first inASC
sorts and last inDESC
sorts. - Floating point data types: see Floating Point Semantics on ordering and grouping.
- If
DISTINCT
is also specified, then the sort key must be the same asexpression
. - If
ORDER BY
is not specified, the order of the elements in the output array is non deterministic, which means you might receive a different result each time you use this function. LIMIT
: Specifies the maximum number ofexpression
inputs in the result. The limitn
must be a constant INT64.
Returns: ARRAY
letter | array_agg |
---|---|
A | [3, 2] |
A | [3, 2] |
B | [7] |
Practical Info
- If there are 0 input rows, the function returns
NULL
.
Common Questions
How do I aggregate an ARRAY with NULL values in the input?
To create an ARRAY
while ignore NULL
s, we can use the IGNORE NULL
parameter in the ARRAY_AGG
function:
Loading code...
array_agg |
---|
[-2, 1] |
Troubleshooting Common Errors
Array cannot have a null element; error in writing field
If you're using ARRAY_AGG
with NULL
values, don't forget to add the IGNORE NULLS
keyword to your query:
Loading code...
Related Pages
- Data Types in Standard SQL
ARRAY_AGG(number IGNORE NULLS)