Accessing ARRAY elements in BigQuery

So here's 3 ways we can access elements in a BigQuery array.

- by index: array[index], starting at 0
- using OFFSET(index): array[OFFSET(index)], also starting at 0
- using ORDINAL(1-based index)), starting at 1

The above will return an "index out of range" error if they are out of bounds, so to get around that you can using SAFE_OFFSET and SAFE_ORDINAL.

If you'd like to see what position each elements resides at in the array, check WITH OFFSET.

WITH input_data AS (


  SELECT ['a','b','c','d','e'] AS letters
)

SELECT

  letters[ORDINAL(2)] AS second_with_ordinal, -- 1 based

  letters[1]  second_with_index, -- 0-based

  letters[OFFSET(1)] second_with_offest, --0-based

  letters[SAFE_OFFSET(5)]  AS sixth_with_safe_offset, -- 0-based, safe: returns NULL when out of bounds

  letters[SAFE_ORDINAL(6)] AS sixth_with_safe_ordinal -- 1-based, safe: returns NULL when out of bounds

FROM input_data

---

Enjoyed this? Here are some related articles you might find useful:

• Using ARRAY_AGG in BigQuery
• UNNESTING ARRAYS in BigQuery
• Leveraging ARRAYS in BigQuery for query performance
• Enumerating ARRAY elements in BigQuery using WITH OFFSET