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.

Found it useful? Check out to my Analytics newsletter at notjustsql.com.