So if you're looking to decompress after a long week and relax, check out the LAX conversion functions for handling JSON conversions in BigQuery.
There are 4 separate functions: LAX_STRING, LAX_BOOL, LAX_FLOAT64, LAX_INT64 - with each one of them attempting to convert a JSON value into the respective datatype.
This is a bit like using SAFE_CAST - you won't get an error if the casting fails, just a NULL (so watch out, check the comments for an example when this can come to back to bite you).
Just note that even JSON-like string won't work as an input, it only works for the native JSON type.
As usual, watch out because these conversion functions might work differently as how you'd expect. SAFE_CAST('1' AS BOOL) => NULL but SAFE_CAST(1 AS BOOL) => TRUE.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.