BeginnerSQL · 25 min

Semi-Structured Data: Beginner

Semi-structured data is in every modern data engineering workload. Event streams emit JSON. Third-party APIs return JSON. Snowflake's VARIANT, BigQuery's JSON type, Postgres's JSONB, Iceberg's STRUCT, and Spark's MapType all store schemaless or partially-schemaless data. The skill is extracting structured values from semi-structured columns: a scalar from a JSON object, elements from an array, fields from a nested struct. This lesson teaches the patterns and the engine-specific syntax that catches newcomers.
list
Extract scalar values from JSON columns with engine-appropriate functions
chart
Navigate nested paths through JSON objects and arrays
branch
UNNEST arrays into rows so you can join them to other tables
code
Distinguish missing keys from explicit null values; engine behaviors differ

When the Schema Is Inside the Data

Daily Life
Interviews

Recognize semi-structured data questions: JSON payloads, nested arrays, event properties, API responses.

The question that recurs in interviews involving event data: 'we have an events table with a JSON payload column. Pull out the user_id and event_type from the payload and count events per user.' The candidate who knows the right extraction syntax for the engine produces a clean six-line query; the candidate who does not fumbles between the various JSON functions. The patterns are small but engine-specific; knowing which one applies is the move.

The mental model: semi-structured is opaque until you extract

A JSON column holds a value the database does not interpret as relational. Until you extract a path, you cannot filter, group, or aggregate on the content. Extraction returns a typed scalar (a STRING, INT, BOOLEAN) that downstream SQL operates on normally. The extraction step is the bridge between the semi-structured world and the relational world. State this when designing: 'I'll extract the path I need into named columns in a CTE, then the rest of the query operates on the extracted columns.'
The mental model: extract bridges semi-structured to relational
  • ▸Semi-structured columns are opaque to SQL until extracted
  • ▸Extraction returns a typed scalar (STRING, INT, BOOLEAN)
  • ▸Downstream SQL operates on the extracted value as native
  • ▸The CTE that does the extraction is the boundary

JSON_EXTRACT and Path Navigation

Daily Life
Interviews

Extract scalar values from nested JSON using dot-notation and bracket-notation path expressions.

Every engine has functions for extracting a scalar from a JSON column. The syntax differs, but the semantics are the same: given a JSON column and a path, return the value at that path as a typed scalar. Knowing the right syntax for the engine you're on is the prerequisite to writing the query.
  • payload->>'field' for text; payload->'field' for nested navigation.
  • payload:field with optional ::TYPE cast.
  • JSON_VALUE(payload, '$.field') for scalars; JSON_QUERY for nested.
  • get_json_object(payload, '$.field') for any depth.

Engine-specific extraction syntax

SELECT
payload ->> 'user_id' AS user_id,
payload ->> 'event_type' AS event_type
FROM events ;
SELECT
payload : user_id :: STRING AS user_id,
payload : event_type :: STRING AS event_type
FROM events ;
SELECT
JSON_VALUE(payload, '$.user_id') AS user_id,
JSON_VALUE(payload, '$.event_type') AS event_type
FROM events ;
SELECT
get_json_object(payload, '$.user_id') AS user_id,
get_json_object(payload, '$.event_type') AS event_type
FROM events ;

Path syntax

Most engines use a $.field path syntax for JSON paths (the $ refers to the root of the document; .field navigates into a property). Postgres's ->> operator takes a string field name directly. Snowflake's : (colon) syntax navigates by field name. The path syntax is engine-specific but the model is the same: walk the path from the root to the value you want.

Casting the result

Extraction usually returns a string. If the value is a number, boolean, or date, cast it to the appropriate type. Postgres: (payload->>'amount')::NUMERIC. Snowflake: payload:amount::FLOAT. BigQuery: SAFE_CAST(JSON_VALUE(payload, '$.amount') AS FLOAT64). The casts let downstream SQL operate on the value as the right type. State this when designing: 'I cast the extracted value to its actual type in the source CTE; downstream code treats it as the native type.'

The query

WITH extracted AS(SELECT payload ->> 'user_id' AS user_id, payload ->> 'event_type' AS event_type, (payload ->> 'amount') :: NUMERIC AS amount FROM events)
SELECT
user_id,
event_type,
COUNT(*) AS event_count
FROM extracted
GROUP BY user_id, event_type
ORDER BY event_count DESC ;

Why the CTE

Extracting in a CTE keeps the extraction logic in one place and the analytic logic separate. The downstream query references the extracted columns as if they were native; if the extraction syntax changes (engine migration, schema change), only the CTE needs to change. Without the CTE, every reference to user_id repeats the extraction; the query becomes harder to read and harder to maintain. State this when writing: 'extract in a CTE; aggregate downstream. The separation is the discipline.'
•Inline extraction throughout the query
  • Extraction syntax repeated wherever the field is used
  • Hard to read; engine-specific syntax interleaved with analytic logic
  • Schema changes require finding every reference
  • Hard to test; can only run the full query
✓CTE-based extraction once at the top
  • Extraction is named in a CTE; references use the column name
  • Easy to read; analytic logic operates on column names
  • Schema changes touch one CTE
  • Can test the CTE independently by SELECTing from it

Pulling a Scalar Field Out of JSON

Daily Life
Interviews

Use UNNEST/LATERAL FLATTEN to explode array columns into rows and join back to the parent.

JSON payloads are nested. A user object contains a profile object which contains an address object. Events have nested device, session, and context structures. The extraction path walks the nesting; each step is one property navigation. Arrays inside the path are referenced by index.

Nested paths

SELECT
payload -> 'device' ->> 'platform' AS platform
FROM events ;
SELECT
payload : device.platform :: STRING AS platform
FROM events ;
SELECT
JSON_VALUE(payload, '$.device.platform') AS platform
FROM events ;

Two operators in Postgres: -> vs ->>

Postgres has two extraction operators. -> returns a JSON value (which can be further navigated). ->> returns a text value (which cannot be further navigated). When walking a nested path, use -> for the intermediate steps and ->> only at the final step. payload->'device'->>'platform' navigates into the device object (->) then extracts the platform field as text (->>). Using ->> at an intermediate step fails because text cannot be navigated further. State this distinction: '-> for navigation, ->> for the final text extraction.'
Postgres -> vs ->> at a glance:
  • ▸-> returns a JSON value (further navigable)
  • ▸->> returns a text value (final extraction)
  • ▸Navigate intermediate paths with ->
  • ▸Extract scalar with ->> only at the final step

Array elements by index

SELECT
payload -> 'items' -> 0 ->> 'product_id' AS first_product_id
FROM events ;
SELECT
payload : items [ 0 ].product_id :: STRING AS first_product_id
FROM events ;
SELECT
JSON_VALUE(payload, '$.items[0].product_id') AS first_product_id
FROM events ;
Array elements are referenced by integer index, 0-based. The same nesting rules apply: navigate with -> or :, extract the final scalar with ->>. For the first item, use [0]; for the last item, most engines support negative indexing or array-length-based access. State this when writing: 'the path is items index 0 then product_id; my engine's syntax follows.'

Strict vs lax extraction

If the path does not exist (the device field is missing on some events), extraction returns NULL. This is lax extraction: the engine silently returns NULL for missing paths. Strict extraction would error on missing paths; most engines default to lax. BigQuery has JSON_VALUE_ARRAY (lax) vs JSON_QUERY_ARRAY (also lax) with options for strictness; Snowflake's : navigation is lax. State this when designing: 'extraction returns NULL for missing paths; we handle NULL downstream with COALESCE or filter it out.'

JSON Columns vs Flat Columns

Daily Life
Interviews

Aggregate over unnested data and reconstruct arrays/structs using ARRAY_AGG and STRUCT.

When the JSON payload contains an array (an order with multiple items), extracting a single element only gets you one item. The right pattern is to UNNEST the array into rows so each item becomes its own row. Then the standard relational operations (join, aggregate, filter) work as usual on the unnested rows.

The UNNEST pattern

SELECT
e.event_id,
e.user_id,
item ->> 'product_id' AS product_id,
(item ->> 'quantity') :: INT AS quantity
FROM events e, LATERAL jsonb_array_elements(e.payload -> 'items') AS item ;
SELECT
e.event_id,
e.user_id,
f.value : product_id :: STRING AS product_id,
f.value : quantity :: INT AS quantity
FROM events e, LATERAL FLATTEN(input = > e.payload : items) f ;
SELECT
e.event_id,
e.user_id,
JSON_VALUE(item, '$.product_id') AS product_id,
CAST(JSON_VALUE(item, '$.quantity') AS INT64) AS quantity
FROM events e, UNNEST(JSON_QUERY_ARRAY(e.payload, '$.items')) AS item ;

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.
  • LATERAL jsonb_array_elements(payload->'items') AS item.
  • LATERAL FLATTEN(input => payload:items) f; reference f.value:field.
  • UNNEST(JSON_QUERY_ARRAY(payload, '$.items')) AS item.

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 the parent row's array column.'

Per-event aggregates after UNNEST

After UNNEST, the row count is per-item. If you SUM(quantity) without grouping, you get the total quantity across all events. If you SUM(quantity) GROUP BY event_id, you get the per-event quantity. The grain shifted; aggregates need to be grain-aware. State this when designing: 'after UNNEST the grain is per-item; per-event aggregates need GROUP BY event_id.'

Reading a Nested Path Expression

Daily Life
Interviews

Discuss with the interviewer when to parse JSON at query time vs materializing into typed columns during ETL.

Three gotchas trip people up when working with semi-structured columns. Each has a specific failure mode and a known mitigation.
Missing keys return NULL silently (lax extraction default)Type coercion errors on dirty data; use TRY_CAST / SAFE_CASTMissing array vs empty array; UNNEST returns zero rows for bothEngine syntax varies; the model is the same

Gotcha 1: missing keys return NULL silently

If the path doesn't exist on a row (the device field is only present on mobile events, not web), extraction returns NULL. Aggregates that don't handle NULL produce wrong numbers (AVG skips NULL rows; COUNT(field) counts non-NULL only). Always check whether the path is consistently populated; if not, use COALESCE for defaults or filter out the NULL rows depending on intent.

Gotcha 2: type coercion errors

Casting an extracted value to a numeric type fails when the value isn't a number ('N/A' string, empty string). Postgres throws; Snowflake's TRY_CAST returns NULL; BigQuery's SAFE_CAST returns NULL. Use the safe variant when the data might be dirty: TRY_CAST or SAFE_CAST instead of raw CAST.

Gotcha 3: array length and missing arrays

If the array is missing entirely (not just empty), UNNEST returns zero rows for that parent. This means events without an items array don't contribute to the unnested result. Often this is what you want (filter to events with items); sometimes it isn't (you want one row per event with NULL items columns). LEFT JOIN against the UNNEST handles the latter; state the intent when writing.
At Vercel in 2024, the analytics team standardized on Snowflake's TRY_CAST plus COALESCE for every JSON extraction in the warehouse. The previous codebase used raw CAST, and silent failures were producing missing data: rows where the cast failed silently were being filtered out by downstream WHERE clauses, leading to under-counted metrics. The migration touched 120 dbt models. The runbook line is 'every JSON extraction in this codebase uses TRY_CAST when the source might be dirty; raw CAST is reserved for known-clean extractions from validated sources.' The candidate who reaches for TRY_CAST and COALESCE unprompted reads as someone who has been on the wrong side of a silent-cast failure.

The closing thought

Semi-structured extraction is engine-specific syntax with consistent semantics. Extract in a CTE; cast safely; UNNEST when you need per-element rows; handle missing keys with COALESCE. The patterns are small; knowing the engine's syntax is the prerequisite. The candidate who fluently writes the extraction for the engine they're on is the candidate who has shipped against event data; the candidate who fumbles the syntax is the one who has only worked with relational sources.
❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at an analytics platform. The interviewer asks: 'Our events table has a JSON payload column. Pull out user_id and event_type from the payload and count events per user.'

You ask the engine first: 'Postgres, Snowflake, BigQuery? The extraction syntax differs.'
On Postgres: extract in a CTE with payload->>'user_id' and payload->>'event_type'. Cast amount with (payload->>'amount')::NUMERIC.
On Snowflake: VARIANT colon syntax. payload:user_id::STRING and payload:event_type::STRING.
On BigQuery: JSON_VALUE(payload, '$.user_id') and JSON_VALUE(payload, '$.event_type'); SAFE_CAST when the source might be dirty.
Follow-up: 'There's an items array; flatten it into one row per item.' You say: 'UNNEST with LATERAL, or FLATTEN on Snowflake, or UNNEST + JSON_QUERY_ARRAY on BigQuery. The result is per-item; per-event aggregates need GROUP BY event_id afterward.'
Closing: 'Extract in a CTE; safe-cast when sources are dirty; UNNEST for per-element rows. The patterns are small; the engine's syntax is the prerequisite.'
KEY TAKEAWAYS
A JSON column is opaque to the engine until a path is extracted; extraction returns a typed scalar and is the bridge from semi-structured to relational, so filtering and grouping happen on extracted columns, never on the raw payload.
Extract in a CTE with named columns. One place owns the engine-specific syntax, so an engine migration or schema change touches only the CTE instead of every reference downstream.
In Postgres, -> returns JSON you can keep navigating and ->> returns text you cannot, so a nested path uses -> for intermediate steps and ->> only at the last one.
Extraction is lax by default: a missing path returns NULL silently, which quietly skews AVG and undercounts COUNT on that column. Check whether the path is consistently populated and reach for COALESCE when it is not.
Cast with TRY_CAST or SAFE_CAST on dirty payloads. A raw cast on an 'N/A' or empty string throws in Postgres, and the safe variants return NULL instead of failing the whole query.
Unnesting shifts the grain to one row per array element, so per-parent aggregates need an explicit GROUP BY on the parent key, and a missing array yields zero rows for that parent unless you LEFT JOIN against the unnest.

JSON columns in SQL interviews separate data engineers from analysts

Category
SQL
Difficulty
beginner
Duration
25 minutes
Challenges
0 hands-on challenges

Topics covered: When the Schema Is Inside the Data, JSON_EXTRACT and Path Navigation, Pulling a Scalar Field Out of JSON, JSON Columns vs Flat Columns, Reading a Nested Path Expression

Lesson Sections

  1. When the Schema Is Inside the Data (concepts: sqlJsonExtract)

    The mental model: semi-structured is opaque until you extract A JSON column holds a value the database does not interpret as relational. Until you extract a path, you cannot filter, group, or aggregate on the content. Extraction returns a typed scalar (a STRING, INT, BOOLEAN) that downstream SQL operates on normally. The extraction step is the bridge between the semi-structured world and the relational world. State this when designing: 'I'll extract the path I need into named columns in a CTE, t

  2. JSON_EXTRACT and Path Navigation (concepts: sqlJsonExtract)

    Every engine has functions for extracting a scalar from a JSON column. The syntax differs, but the semantics are the same: given a JSON column and a path, return the value at that path as a typed scalar. Knowing the right syntax for the engine you're on is the prerequisite to writing the query. Engine-specific extraction syntax Path syntax Most engines use a $.field path syntax for JSON paths (the $ refers to the root of the document; .field navigates into a property). Postgres's ->> operator ta

  3. Pulling a Scalar Field Out of JSON (concepts: sqlJsonExtract)

    JSON payloads are nested. A user object contains a profile object which contains an address object. Events have nested device, session, and context structures. The extraction path walks the nesting; each step is one property navigation. Arrays inside the path are referenced by index. Nested paths Two operators in Postgres: -> vs ->> Postgres has two extraction operators. -> returns a JSON value (which can be further navigated). ->> returns a text value (which cannot be further navigated). When w

  4. JSON Columns vs Flat Columns (concepts: 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

  5. Reading a Nested Path Expression (concepts: sqlJsonExtract)

    Three gotchas trip people up when working with semi-structured columns. Each has a specific failure mode and a known mitigation. Gotcha 1: missing keys return NULL silently If the path doesn't exist on a row (the device field is only present on mobile events, not web), extraction returns NULL. Aggregates that don't handle NULL produce wrong numbers (AVG skips NULL rows; COUNT(field) counts non-NULL only). Always check whether the path is consistently populated; if not, use COALESCE for defaults