JSON Extraction Patterns

Concepts covered: JSON Extraction

Most event-driven systems emit JSON in a few predictable shapes, and knowing them makes extraction queries easier to write.

Fixed envelope, flexible payload

Event logs typically have a fixed outer structure with a flexible payload:

{
  "timestamp": "2024-01-15T10:30:00Z",
  "event_name": "page_view",
  "properties": {
    "page": "/products/widget",
    "referrer": "bing.com",
    "duration_ms": 4500
  }
}

SELECT
  JSON_EXTRACT(event_data, '$.event_name') AS event,
  JSON_EXTRACT(event_data, '$.properties.page') AS page,
  JSON_EXTRACT(event_data, '$.properties.duration_ms') AS duration
FROM event_logs
WHERE JSON_EXTRACT(event_data, '$.event_name') = '"page_view"'

event_logs
id	event_data
1	{"event_name": "page_view", "properties": {"page": "/products/widget", "duration_ms": 4500}}
2	{"event_name": "click", "properties": {"page": "/products/widget", "element": "add_to_cart"}}
3	{"event_name": "purchase", "properties": {"order_id": 1042, "amount": 59.99}}

Result
event	page	duration
"page_view"	"/products/widget"	4500

Note the WHERE clause comparison uses '"page_view"' with escaped quotes because JSON_EXTRACT returns JSON-formatted strings that include their surrounding quotes.

Missing paths

When a requested path does not exist, JSON_EXTRACT returns a JSON null. This differs from SQL NULL but can be treated similarly in most contexts.

SELECT
  event_id,
  JSON_EXTRACT(payload, '$.optional_field') AS optional_value
FROM events

events
event_id	event_type	payload
1	page_view	{"optional_field": "exists", "page": "/home"}
2	click	{"page": "/pricing"}
3	page_view	{"page": "/about", "optional_field": null}

Result
event_id	optional_value
1	"exists"
2	NULL
3	null

Rows where the field does not exist show null (JSON null). Downstream steps should either filter these rows out or fill in default values.

How should you handle a JSON field that might not exist in every row?

events
id	event_type	data
1	signup	{"user": "ana", "optional": "exists"}
2	login	{"user": "ben"}
3	logout	{"user": "cy", "device": "mobile"}

Unguarded

Without protection, missing fields return null, which can cause issues in downstream aggregations or joins.

SELECT
  JSON_EXTRACT(data, '$.optional') AS optional
FROM events

Result
optional
"exists"
NULL
NULL

COALESCE

COALESCE provides a fallback value when the path does not exist, making your queries resilient to incomplete JSON documents.

SELECT
  COALESCE(
    JSON_EXTRACT_SCALAR(data, '$.optional'),
    'unknown'
  ) AS safe_value
FROM events

Result
safe_value
exists
unknown
unknown

Path patterns to remember

Three path forms cover the majority of real-world JSON extraction: $.field for top-level keys, $.parent.child for nested objects, and $.array[0] for array elements (0-indexed in path syntax, 1-indexed with bracket notation).

When a path doesn't match, JSON_EXTRACT returns JSON null rather than SQL NULL. Wrapping with COALESCE handles missing paths, and event data needs that because not every row shares the same structure.

About this lesson

This section is part of the Complex Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.