Semi-Structured Data & JSON in SQL
Real pipelines increasingly ingest data that doesn't arrive as flat columns — API responses, event/clickstream payloads. Every modern engine queries JSON directly with SQL.
No separate parsing step needed — JSON lives inside ordinary SQL:
- JSON is stored as ordinary
TEXT/VARIANT; the engine parses it at query time. json_extract(col, '$.path')(or the->>shorthand) pulls a single field out.- A table-valued function (
json_eachin SQLite) turns a JSON array into one row per element — joinable like any other table. - Once extracted, a JSON field behaves like any other column: GROUP BY, AVG, CASE WHEN all work on it.
Validate Before You Extract
Semi-structured data is any value that doesn't fit neatly into flat columns — an API response, an event payload, a webhook body — typically arriving as JSON. Storing it as plain text and parsing it at query time (rather than forcing it into a rigid table schema up front) is what lets a pipeline ingest data whose exact shape you don't fully control or that varies event to event. json_valid() checks whether a string is well-formed JSON before you rely on it — especially important for data from an external API, since a source you don't control can and eventually will send a malformed payload. Checking validity explicitly means a bad payload fails loudly and gets flagged, instead of silently returning NULL for every field you try to extract from it and only being noticed much later, in a report that looks quietly wrong.
SQL · storing + validating + extracting JSON
CREATE TABLE customer_events (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
event_type VARCHAR(30) NOT NULL,
payload TEXT NOT NULL -- JSON blob: shape varies by event_type
);
INSERT INTO customer_events (id, customer_id, event_type, payload) VALUES
(3, 7, 'page_view', '{"page": "/checkout", "device": "desktop", "duration_sec": 118}');
SELECT id, event_type, json_valid(payload) AS is_valid_json
FROM customer_events;
-- json_extract pulls a single field out; ->> is the shorthand for the same thing
SELECT id, event_type,
json_extract(payload, '$.device') AS device,
payload ->> '$.duration_sec' AS duration_sec
FROM customer_events
WHERE event_type = 'page_view';Once extracted, a JSON field behaves like any other column — GROUP BY, AVG, CASE WHEN all work on it.
Unnesting an Array
A JSON payload often holds an array nested inside it — a purchase event's list of line items, an order's list of products — and that array can't be queried, grouped, or joined while it's still a single blob. Unnesting ("flattening") turns that array into one row per element, so each item becomes an ordinary row that can be joined, filtered, and aggregated exactly like data from a real table. SQLite's version of this is json_each(), a table-valued function: it takes a JSON array and returns a virtual table with one row per element, which you then join against in the FROM clause like any other table. Part 12 maps this same idea onto BigQuery's UNNEST(), Snowflake's LATERAL FLATTEN, and Databricks' EXPLODE() — one concept, four names, because every platform with native nested types needs a way to turn "array" into "rows."
SQL · json_each unnests an array
-- payload: {"order_total": 174.98, "items": [{"product_id": 13, "qty": 1}, {"product_id": 15, "qty": 1}]}
SELECT ce.id AS event_id, ce.customer_id,
je.value ->> '$.product_id' AS product_id,
je.value ->> '$.qty' AS quantity
FROM customer_events ce, json_each(ce.payload, '$.items') je
WHERE ce.event_type = 'purchase';Extract What You Query Often
json_extract() (or its ->> shorthand) has a real, recurring cost: every time it's called, the engine re-parses the whole JSON blob just to pull out one field, on every row, on every query. If a field is queried constantly — grouped by, filtered on, joined against — that repeated parsing adds up. The fix is to pull the fields you query repeatedly out into real, typed columns once (a view, Part 1, or a dbt staging model, Part 10), so every downstream query reads an ordinary pre-extracted column instead of re-parsing JSON from scratch each time. Keep the raw payload column around too, untouched, for the fields you only need occasionally or haven't anticipated needing yet — the extraction is an optimization for the hot paths, not a replacement for keeping the original data.
SQL · aggregating an extracted field
SELECT json_extract(payload, '$.device') AS device,
COUNT(*) AS views,
ROUND(AVG(json_extract(payload, '$.duration_sec')), 1) AS avg_duration_sec
FROM customer_events
WHERE event_type = 'page_view'
GROUP BY device;Once extracted, a JSON field behaves like any other column.
The takeaway: json_each (or whatever a given platform calls it) is the local name for a pattern every cloud platform implements under a different name — the underlying idea, unnesting a nested structure into rows, is the transferable part.
Runnable code for this part: sql-for-data-platforms on GitHub — Module 6 of 14.
← Back to Publications