JSON_EXTRACT()

Concepts covered: JSON Extraction

JSON_EXTRACT retrieves a value from a JSON document using a path expression. JSON, which stands for JavaScript Object Notation, is a text-based format for representing structured data. It has become the dominant format for API responses, event logs, and configuration files across the industry.

A JSON document consists of key-value pairs enclosed in curly braces. Keys are strings in double quotes, followed by a colon and a value. Values can be strings, numbers, booleans, arrays, nested objects, or the special value null.

Understanding JSON Structure

Consider this JSON document stored in a single database column:

{
  "user_id": 12345,
  "event_type": "purchase",
  "details": {
    "product": "Premium Plan",
    "amount": 99.99
  },
  "tags": ["mobile", "ios", "returning"]
}

This single JSON value contains a user ID (number), an event type (string), nested details (object with its own keys), and tags (array of strings). All of this lives in one column of one row.

Path Syntax & Navigation

The syntax is JSON_EXTRACT(json_column, path). The path uses dot notation starting with $ to represent the root. For example, '$.user_id' extracts the user_id field from the root level.

SELECT
  event_id,
  JSON_EXTRACT(payload, '$.user') AS user_name,
  JSON_EXTRACT(payload, '$.action') AS action_type
FROM events

Notice that JSON_EXTRACT returns JSON-formatted values. String results include the surrounding double quotes. This preserves JSON semantics but requires additional processing if you need plain strings.

Nested Path Access

JSON documents often contain nested objects. Use additional dots to traverse deeper into the structure:

SELECT
  JSON_EXTRACT(payload, '$.details.product') AS product,
  JSON_EXTRACT(payload, '$.details.amount') AS amount
FROM transactions

The path $.details.product first navigates to the details object, then extracts the product field within it. You can nest as deeply as your JSON structure requires.

Which path expression reaches the nested amount field?

$.amount

This only checks the root level. If amount is nested inside details, this path returns null.

SELECT JSON_EXTRACT(payload, '$.amount')
FROM transactions

Nested Path

Correct. The dot notation traverses into the details object first, then retrieves the amount field.

SELECT JSON_EXTRACT(payload, '$.details.amount')
FROM transactions

Array Index Access

JSON_EXTRACT can also access array elements by index. Use bracket notation with the zero-based index:

SELECT
  JSON_EXTRACT(payload, '$.tags[0]') AS first_tag,
  JSON_EXTRACT(payload, '$.tags[1]') AS second_tag
FROM events

Array indices in JSON paths are zero-based, meaning the first element is at index 0. Accessing an index that does not exist returns a JSON null value.

Root Field

Extracting a root-level field: $.user_id accesses user_id directly from the JSON root.

SELECT JSON_EXTRACT(data, '$.user_id')
FROM events

Nested Field

Extracting a nested field: $.address.city first enters the address object, then retrieves city.

SELECT JSON_EXTRACT(data, '$.address.city')
FROM users

Array Elem

Extracting from an array of objects: $.items[0].name gets the name from the first item in the items array.

SELECT JSON_EXTRACT(data, '$.items[0].name')
FROM orders

Best Practices

Knowing when to use JSON functions and how they execute helps you write efficient queries and structure your data appropriately.

When to Use JSON_EXTRACT

JSON_EXTRACT is most valuable when dealing with semi-structured data that varies in shape or comes from external systems.

• Event Logs: Ingest raw event JSON
• API Responses: Parse vendor payloads
• Config Data: Flexible key-value pairs
• Telemetry: Sensor and app signals
• Webhooks: Third-party callbacks

As your pipeline matures, consider whether raw JSON flexibility is still worth the query cost.

Database Execution

Understanding how the database processes JSON helps explain why certain patterns perform better than others.

How JSON_EXTRACT Works Under the Hood

• Parses the JSON string for each row at query time
• Path navigation traverses the parsed structure
• Returns JSON-typed values (strings include quotes)
• Missing paths return JSON null, not SQL NULL

JSON parsing has computational cost. For large tables with complex JSON documents, consider materializing frequently queried paths into dedicated columns during ETL (Extract, Transform, Load) processing.

JSON_EXTRACT vs JSON_EXTRACT_SCALAR

JSON_EXTRACT returns a JSON-typed value. Strings come back with surrounding quotes (e.g. "alice"). JSON_EXTRACT_SCALAR strips the quotes and returns a plain SQL string (e.g. alice). Use SCALAR when the value feeds into comparisons, joins, or display.

A practical rule: if the extracted value will be compared to a string literal or joined to another column, use JSON_EXTRACT_SCALAR. If you are passing the result directly to another JSON function, JSON_EXTRACT preserves the type information you need.

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.