Datawise
BigQuery Arrays & Structs

Enumerating ARRAY elements in BigQuery using WITH OFFSET

Constantin LunguUpdated 1 min read

Photo by Anne Nygård on Unsplash

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.

WITH input_data AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'order_created' AS event_type, TIMESTAMP '2021-01-01 10:00:00' AS event_time
  UNION ALL
  SELECT 1 AS customer_id, 100 AS order_id, 'order_paid' AS event_type, '2021-01-01 10:01:05' AS event_time
  UNION ALL
  SELECT 1 AS customer_id, 100 AS order_id, 'order_shipped' AS event_type, '2021-01-01 18:30:00' AS event_time
  UNION ALL
  SELECT 2 AS customer_id, 200 AS order_id, 'order_created' AS event_type, TIMESTAMP '2021-02-01 11:01:00' AS event_time
  UNION ALL
  SELECT 2 AS customer_id, 200 AS order_id, 'order_paid' AS event_type, '2021-02-01 11:01:45' AS event_time
  UNION ALL
  SELECT 2 AS customer_id, 200 AS order_id, 'order_shipped' AS event_type, '2021-02-01 14:35:00' AS event_time
), nested_data AS (
SELECT
  customer_id,
  order_id,
  ARRAY_AGG(event_type ORDER BY event_time) AS status_updates
FROM input_data
GROUP BY customer_id, order_id)

SELECT customer_id, order_id, status_update, offset

FROM nested_data

LEFT JOIN UNNEST(status_updates) AS status_update

WITH OFFSET AS offset

BigQuery results: for customer 1 / order 100 and customer 2 / order 200, status_update order_created, order_paid and order_shipped with offset 0, 1 and 2.

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