How do you query deeply nested JSON with arrays in SQL, and when do you flatten vs keep it nested?

A flat path extract is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows and a clear rule for when to flatten vs query in place.

Updated Aug 2026 · Grounded in real Applied AI Engineer interview loops and written to a senior-engineer editorial bar.