# Extracting keys from JSON in BigQuery


[A couple of months ago](https://datawise.dev/dynamically-extracting-json-data-in-bigquery), I've posted about dynamically extracting key-value pairs from JSON in BigQuery SQL which leveraged regex (check comments).

Shortly after that post, we've gotten a new built-in function to dynamically extract the keys occurring in a JSON. It allows us to retrieve all the keys occurring in a JSON value, with a few controls on how this is done.

The function is JSON\_KEYS. Apart from the json input, we can tweak:  
\- max\_depth: for many levels of nesting we should go through to extract keys  
\- mode: strict/lax/lax recursive - controls if we extract keys from arrays.

The usual note - still in preview.

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

*Found it useful? Subscribe to my Analytics newsletter at* [https://www.notjustsql.com](https://www.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)
- [LAX JSON conversion functions in BigQuery](https://datawise.dev/lax-json-conversion-functions-in-bigquery)