Complex Data: Beginner

Pull values from JSON columns

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.

Consider this JSON document stored in a single database column:

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.

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.

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.

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

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.

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

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.

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

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

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

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

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.

SELECT
  event_id,
  ___(
    ___,
    ___
    ) AS user_id
FROM event_data

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.

Navigate nested JSON structures

Understanding common JSON structures in analytics data helps you write more effective extraction queries. Most event-driven systems follow predictable patterns.

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

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.

Rows where the field does not exist show null (JSON null). Your downstream processing should handle these gracefully, either by filtering them out or providing default values.

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

SELECT
  event_id,
  ___ 1 AS first_tag
FROM event_data

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 does not match, JSON_EXTRACT returns JSON null rather than SQL NULL. Wrapping with COALESCE handles missing paths gracefully, which matters for event data where not every row shares the same structure.

Store and access ordered collections

In SQL, arrays are denoted with the ARRAY keyword and square brackets. Each element has a position (index), and elements are accessed using bracket notation. Unlike JSON arrays which are stored as text, native SQL arrays are stored in a binary format optimized for database operations.

Arrays can be created inline using the ARRAY constructor:

The ARRAY keyword followed by values in square brackets creates an array literal. Elements are separated by commas and can be of any data type, though all elements in an array must share the same type.

SQL array indices are typically 1-based, meaning the first element is at position 1. This differs from JSON paths (0-based) and most programming languages. Accessing an index beyond the array bounds returns NULL.

Product tags

Categories or labels for products.

User permissions

Role assignments for access control.

Order line items

Simple orders with multiple products.

Event properties

Multiple values for analytics events.

Search keywords

Filter selections and search terms.

SELECT
  event_id,
  ___ 'browser' AS browser
FROM event_data

Arrays work well when the number of values is variable but each value is the same type: tags on a post, items in a cart, permissions on a role. If the values have different types or each key has a distinct meaning, a map is usually the better fit.

Work with key-value pairs in columns

Maps can be created using the MAP constructor with alternating keys and values:

The MAP function takes two arrays: one of keys and one of values. Keys must be unique within a map. Both keys and values have specific types, and all keys must share one type while all values share another.

The bracket notation properties['browser'] retrieves the value associated with the key 'browser'. If the key does not exist in the map, the result is NULL.

Maps and JSON objects look similar but serve different purposes:

Maps excel when you need to store and retrieve named properties efficiently without the parsing overhead of JSON.

Convert between JSON and SQL types

CAST converts data from one type to another. When working with complex data structures, CAST enables conversion between JSON strings and native SQL arrays or maps, allowing you to leverage the best features of each format.

The syntax is CAST(expression AS target_type). For complex types, the target type includes the element type for arrays or the key and value types for maps.

Convert a JSON array string to a native SQL array for better query performance:

The type ARRAY(VARCHAR) specifies an array of strings. You must provide the element type. Common element types include VARCHAR, INTEGER, DOUBLE, and BOOLEAN.

Convert a JSON object to a native SQL map:

The type MAP(VARCHAR, INTEGER) specifies string keys mapping to integer values. Both the key type and value type must be specified.

A common pattern extracts a JSON array field and converts it to a native array for further processing:

Casting has a computational cost, so avoid casting the same value multiple times. Consider using WITH clauses to cast once and reuse the result.

SELECT
  ___(properties 'count' ___ ___) AS count_num
FROM event_data

CAST bridges the gap between JSON text and native SQL types, enabling you to combine the flexibility of JSON storage with the performance of native operations.