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.

SELECT JSON_FLATTEN(JSON '[1, [2,3,4],[[5,6],[7,8]]]' )

---

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