Flattening JSON arrays in BigQuery

I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN().

It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are.

So where is this actually useful?
➡️ Handling heterogeneous JSON where the nesting depth isn’t consistent
➡️ Cleaning up malformed or jagged arrays
➡️ Normalizing data before UNNEST so you don’t get arrays of arrays

Where I would not use it?

👉 Don’t use it when the hierarchy matters. Flattening removes structural context, so you lose information about where an element came from.

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

---

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

• The JSON datatype in BigQuery
• JSON datatype vs JSON-like STRING in BigQuery
• Extracting keys from JSON in BigQuery
• LAX JSON conversion functions in BigQuery