Here's a mildly interesting function if you're working with JSON in BigQuery.

JSON_TYPE takes in a JSON value and returns the name of the respective JSON type (object, array, string, number, boolean, null) as a STRING.

See below an illustration of it in action.

Also, given we use the native JSON datatype, notice how we can just access the first ([0]) element in an ARRAY or a field directly by dot notation.
This you cannot do with a JSON-like STRING (not without parsing). Check out my previous post about JSON vs JSON-like string.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.