Enumerating ARRAY elements in BigQuery using WITH OFFSET
title: "Enumerating ARRAY elements in BigQuery using WITH OFFSET" seoTitle: "BigQuery WITH OFFSET: Get Array Element Index After UNNEST" seoDescription: "WITH OFFSET in BigQuery retrieves the 0-based position of each element in an array after UNNESTing it. Use it to preserve the original array order when..." datePublished: Sun Mar 31 2024 08:00:26 GMT+0000 (Coordinated Universal Time) cuid: cluf8ekzm000608l61vvhf85w slug: enumerating-array-elements-in-bigquery-using-with-offset cover: https://cdn.hashnode.com/res/hashnode/image/stock/unsplash/RF5HrKt8pfk/upload/aa242dd53579b38ce5fa60e37f6155e7.jpeg tags: analytics, databases, google-cloud, sql, bigquery, data-engineering
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.
Enjoyed this? Here are some related articles you might find useful:
