In a previous post we've covered what ARRAYS are in BigQuery, their use cases and how to flatten them with UNNEST.

Quite important to mention, ARRAYS are ordered collections (like lists in Python) - you set up that order when creating it. By UNNESTING them, the order is no longer guaranteed.

In order to retrieve the order in which an element was in an array before UNNESTING (apart from ordering again by something in the array like a timestamp) you can use WITH OFFSET, which will yield an additional column, showing the 0-based index of the element in the original array.

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