Skip to content

Understand Array in BigQuery

Array

In Google BigQuery, an array is an ordered list consisting of zero or more values of the same data type. 

Create an Array

SELECT
['apple', 'banana', 'grape', 'ananas'] AS fruit_array

Notice, although there are four elements in the array, but it contains one row, indeed.

Rowfruit_array
1apple
banana
grape
ananas

If we add a numeric element into the string array, we will get an error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1].

In any array, the elements must have the same data type.

SELECT
['apple', 'banana', 'grape', 'ananas', 123] AS fruit_array

Access array elements

Let’s create an Array with several arrays inside it.

WITH
  Sequences AS (
    SELECT [0, 1, 1, 2, 3, 5] AS numbers UNION ALL
    SELECT [2, 4, 8, 16, 32] UNION ALL
    SELECT [5, 10]
  )
SELECT * FROM Sequences
/*---------------------*
 | numbers             |
 +---------------------+
 | [0, 1, 1, 2, 3, 5]  |
 | [2, 4, 8, 16, 32]   |
 | [5, 10]             |
 *---------------------*/

To access array elements in the Array column, you need to use either index or OFFSET(index) for zero-based indexes, or ORDINAL(index) for one-based indexes.

SELECT
  numbers,
  numbers[0] AS index_0,
  numbers[OFFSET(1)] AS offset_1,
  numbers[ORDINAL(1)] AS ordinal_1
FROM Sequences
/*--------------------+---------+----------+-----------*
 | some_numbers       | index_0 | offset_1 | ordinal_1 |
 +--------------------+---------+----------+-----------+
 | [0, 1, 1, 2, 3, 5] | 0       | 1        | 0         |
 | [2, 4, 8, 16, 32]  | 2       | 4        | 2         |
 | [5, 10]            | 5       | 10       | 5         |
 *--------------------+---------+----------+-----------*

Find Length of Array

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS numbers
   UNION ALL SELECT [5, 10] AS numbers)
SELECT some_numbers,
       ARRAY_LENGTH(numbers) AS len
FROM Sequences;
/*--------------------+--------*
 | numbers            | len    |
 +--------------------+--------+
 | [0, 1, 1, 2, 3, 5] | 6      |
 | [2, 4, 8, 16, 32]  | 5      |
 | [5, 10]            | 2      |
 *--------------------+--------*/

Concatenate Arrays

WITH
  array1 AS (
    SELECT ["apple", "banana", "orange"] AS arr1
  ),
  array2 AS (
    SELECT ["grape", "pineapple"] AS arr2
  )
SELECT ARRAY_CONCAT(arr1, arr2) AS concatenated_array
FROM array1, array2;

Convert Array elements to rows in a table

To convert an ARRAY into a set of rows, also known as “flattening,” use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY.

SELECT *
FROM UNNEST(['apple', 'banana', 'grape', 'ananas'])
  AS fruit
WITH OFFSET AS offset
ORDER BY offset;
/*----------+--------*
 | fruit    | offset |
 +----------+--------+
 | apple    | 0      |
 | banana   | 1      |
 | grape    | 2      |
 | ananas   | 3      |
 *----------+--------*/
Tags: