How do you query deeply nested JSON with arrays in SQL, and when do you flatten vs keep it nested?
Pulling a scalar by path is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows plus a clear rule for when to flatten versus query in place.
Updated Sep 2026 · Grounded in real GenAI, LLM, and AI/ML engineering interview loops and written to a senior-engineer editorial bar.
Pulling a scalar by path is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows plus a clear rule for when to flatten versus query in place.
Lead with where the obvious approach breaks, because that is the judgment they are screening for — most candidates jump straight to the happy path and lose the room.
Then walk the failure back through the pipeline in order, naming the one metric the customer's exec sponsor actually cares about before you propose the fix.
