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 know.

This allows extraction of key-value pairs from each JSON-like string, even if they are don't look the same.

Kudos to someone on Stack Overflow where I've first seen it.

Pretty sure the regex can be streamlined, but that's as much me feat. GPT could do today.

PS. If we're talking about JSON datatype, you can transform it to JSON-like STRING with TO_JSON_STRING() and do the same thing.

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