Feature request: SAFE.ARRAY_FIRST and SAFE.ARRAY_LAST — returning NULL on empty arrays

Google Cloud Data Analytics
1

The problem

I recently discovered ARRAY_FIRST and ARRAY_LAST, which are a fantastic readability improvement over the classic workarounds:

-- Old way: verbose and easy to get wrong
arr[OFFSET(0)]
arr[ORDINAL(1)]
arr[ARRAY_LENGTH(arr) - 1]
ARRAY_REVERSE(arr)[OFFSET(0)]

-- New way: clean and intention-revealing
ARRAY_FIRST(arr)
ARRAY_LAST(arr)

However, both functions throw a runtime error when the array is empty — which is a very common real-world situation (e.g. no matching rows after an ARRAY_AGG with a filter):

:warning: Array index 0 is out of bounds (array length is 0)

This forces us back to defensive workarounds that undo the readability gain entirely:

-- Workaround 1: guard with IF
IF(ARRAY_LENGTH(arr) > 0, ARRAY_FIRST(arr), NULL)

-- Workaround 2: use a subquery / SAFE_OFFSET
arr[SAFE_OFFSET(0)]

The ask

BigQuery already has a SAFE. prefix for functions that returns NULL instead of erroring — a well-established and intuitive pattern. The natural extension is:

-- Return NULL if array is empty, instead of throwing
SAFE.ARRAY_FIRST(arr)
SAFE.ARRAY_LAST(arr)

✓ Returns NULL when arr is empty — consistent with SAFE.DIVIDE, SAFE.PARSE_DATE, etc.

This would complete the feature: the readability of ARRAY_FIRST / ARRAY_LAST could finally be used unconditionally in production queries without defensive boilerplate.

Real-world example

-- Common pattern: first event per user after ARRAY_AGG
SELECT
  user_id,
  SAFE.ARRAY_FIRST(ARRAY_AGG(event_ts ORDER BY event_ts)
    ) AS first_event,
  SAFE.ARRAY_LAST(ARRAY_AGG(event_ts ORDER BY event_ts)
    ) AS last_event
FROM events
GROUP BY user_id

Upvote if you want this

If this would help your BigQuery SQL work, please upvote and share your own use case below. The more concrete examples we provide, the stronger the signal for the product team.

1 Like