Dynamically extracting JSON data in BigQuery

I was asked today about dynamically extracting key-value pairs from heterogeneous JSON-like strings in BigQuery SQL and I've remembered this interesting approach I've seen a while ago. It leverages regular expressions, one of everyone's favorites, I ...