ARRAY_AGG

Definition

The ARRAY_AGG function in BigQuery creates an ARRAY from another expression or table. It is basically the opposite of UNNEST.

Syntax

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

Optional Clauses

The clauses are applied in the following order:

• 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.

Returns: ARRAY

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

Practical Info

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
  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

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:

ARRAY_AGG(number IGNORE NULLS)

Related Pages