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.
Found it useful? Check out to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
