3 Ways to Access Elements in BigQuery Array: Index, OFFSET, and ORDINAL

There are three ways to access elements in a BigQuery array:

1. By index: You can access elements using the syntax array[index], where indexing starts at 0.
2. Using OFFSET: Access elements with array[OFFSET(index)], which also starts at 0.
3. Using ORDINAL: This method uses a 1-based index with the syntax array[ORDINAL(1-based index)], starting at 1.

To prevent 'index out of range' errors in BigQuery, you can use SAFE_OFFSET and SAFE_ORDINAL functions. These functions will return NULL instead of an error if the specified index is out of bounds.

To find the position of elements in a BigQuery array, you can use the WITH OFFSET clause. It will return the position of each element within the array. For more details, you can check the link provided in the comments of the original post.