ARRAY_AGG

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

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:

1. OVER: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within ARRAY_AGG().

1. DISTINCT: Each distinct value of expression is aggregated only once into the result.

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

1. ORDER BY: Specifies the order of the values.

1. For each sort key, the default sort direction is ASC.

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

1. Floating point data types: see Floating Point Semantics on ordering and grouping.

1. If DISTINCT is also specified, then the sort key must be the same as expression.

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

1. LIMIT: Specifies the maximum number of expression inputs in the result. The limit n must be a constant INT64.

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

• If there are 0 input rows, the function returns NULL.

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

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)

• UNNEST

• Data Types in Standard SQL