JSON Columns vs Flat Columns

Concepts covered: sqlUnnest

The UNNEST pattern Reading the pattern UNNEST (or FLATTEN, or jsonb_array_elements) takes an array and produces one row per element. The result is joined back to the parent row (via LATERAL or implicit cross join) so each unnested row has the parent's context. The output has one row per (event, item) pair; aggregates on the result are per-item-level. Why LATERAL LATERAL lets the UNNEST reference columns from the parent table (events). Without LATERAL, the UNNEST is independent and cannot see the per-row array. The LATERAL keyword makes the unnested call dependent on the parent row, which is what we want. Snowflake's FLATTEN serves the same role; BigQuery's UNNEST inside a comma join handles it implicitly. State this when designing: 'LATERAL or its equivalent is what makes the UNNEST see th

About This Interactive Section

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.