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:

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

• OVER: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within ARRAY_AGG().
• DISTINCT: Each distinct value of expression is aggregated only once into the result.
• IGNORE NULLS or RESPECT NULLS: If IGNORE NULLS is specified, the NULL values are excluded from the result. If RESPECT NULLS is specified or if neither is specified, the NULL values are included in the result. An error is raised if an array in the final query result contains a NULL 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 in ASC sorts and last in DESC 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 as expression.
• 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 of expression inputs in the result. The limit n must be a constant INT64.

Returns: ARRAY

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 NULLs, we can use the IGNORE NULL parameter in the ARRAY_AGG function:

SELECT
  letter,
  FORMAT('%T', ARRAY_AGG(number) OVER (PARTITION BY letter)) AS array_agg
FROM
  (
    SELECT
      'A' AS letter,
      3 AS number
    UNION ALL
(    SELECT
      'A' AS letter,
      2 AS number)
    UNION ALL
(    SELECT
      'B' AS letter,
      7 AS number)
  ) AS table_3

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:

SELECT
  FORMAT('%T', ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM
  UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x

Related Pages

• Data Types in Standard SQL

ARRAY_AGG(number IGNORE NULLS)