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

![](https://miro.medium.com/v2/resize:fit:1400/0*fRHBrsGhHg1b0FjQ align="left")

*Found it useful? Subscribe to my Analytics newsletter at* [*notjustsql.com*](https://notjustsql.com)*.*

---

*Enjoyed this? Here are some related articles you might find useful:*

- [The JSON datatype in BigQuery](https://datawise.dev/the-json-datatype-in-bigquery)
- [JSON datatype vs JSON-like STRING in BigQuery](https://datawise.dev/json-datatype-vs-json-like-string-in-bigquery)
- [Flattening JSON arrays in BigQuery](https://datawise.dev/flattening-json-arrays-in-bigquery)
- [Extracting keys from JSON in BigQuery](https://datawise.dev/extracting-keys-from-json-in-bigquery)