Using LAST_VALUE with STRUCTS
Even an “empty” STRUCT is still technically something. Not the same as a standalone NULL value.
This is why, if you work with STRUCTs in SQL and try to find the latest non-empty struct using LAST_VALUE(...) IGNORE NULLS, you’ll notice it doesn’t help — because the struct, even when all fields are null, is still considered non-null.
LAST_VALUE only skips rows where the entire expression itself is NULL.
To fix this, we can adjust the logic in one of the following ways:
➡️ Setting the value to NULL when all fields are NULL
➡️ Using TO_JSON_STRING + NULLIF to treat such entries as “null”
➡️ Using REGEXP_CONTAINS (thanks ChatGPT) for more dynamic checks
Alternatively, we can just apply LAST_VALUE separately to each individual field in the struct.
If you're new to STRUCTs, see one of my previous posts.
SELECT
event_date,
LAST_VALUE(event IGNORE NULLS) OVER (ORDER BY event_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_known_event
FROM cte
ORDER BY event_date
SELECT event_date,
LAST_VALUE(CASE WHEN event.a IS NULL AND event.b IS NULL THEN NULL ELSE event END IGNORE NULLS)
OVER (ORDER BY event_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_known_event_struct,
LAST_VALUE(NULLIF(TO_JSON_STRING(event),'{"a":null,"b":null}') IGNORE NULLS)
OVER (ORDER BY event_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_known_event_json,
LAST_VALUE(CASE WHEN NOT REGEXP_CONTAINS(TO_JSON_STRING(event), r':(true|false|"|[0-9\-]|\[|\{)') THEN NULL ELSE event END IGNORE NULLS)
OVER (ORDER BY event_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS last_known_event_regex
FROM cte
Enjoyed this? Here are some related articles you might find useful: