How to Query Nested JSON Fields in SQL

Modern databases increasingly store semi-structured data in JSON format. APIs, event logs, application data, and customer interactions often arrive as JSON documents rather than traditional rows and columns.

While JSON provides flexibility, analysts and data engineers still need to query specific values buried inside nested structures. Fortunately, most modern SQL databases provide built-in functions for working with JSON data.

To query nested JSON fields in SQL, use JSON extraction functions provided by your database. For example, PostgreSQL uses -> and ->> operators, MySQL uses JSON_EXTRACT(), and SQL Server uses JSON_VALUE() to retrieve values from nested JSON objects.

In this guide, you’ll learn how to query nested JSON fields in SQL, understand common JSON structures, and explore practical examples across popular database platforms.

What Is Nested JSON?

JSON (JavaScript Object Notation) is a popular format for storing structured and semi-structured data.

A simple JSON object might look like:

{
  "customer": "John",
  "city": "Lagos"
}

Nested JSON contains objects inside other objects.

Example:

{
  "customer": {
    "name": "John",
    "address": {
      "city": "Lagos",
      "country": "Nigeria"
    }
  }
}

Here:

• customer is an object
• address is nested within customer
• city is nested within address

Accessing deeply nested values requires JSON query functions.

Why JSON Is Common in Analytics

JSON frequently appears in:

• API responses
• Event tracking systems
• Application logs
• E-commerce transactions
• Customer behavior data
• Data lake environments

Example event record:

{
  "event_id": 101,
  "user": {
    "id": 2001,
    "country": "Nigeria"
  },
  "device": {
    "type": "Mobile",
    "os": "Android"
  }
}

Analysts often need to extract values such as:

• User ID
• Country
• Device type

without flattening the entire JSON document.

Querying JSON in PostgreSQL

PostgreSQL provides excellent JSON support through:

• JSON
• JSONB

data types.

Sample Table

CREATE TABLE customers (
    id INT,
    details JSONB
);

Example JSON:

{
  "name": "John",
  "address": {
    "city": "Lagos",
    "country": "Nigeria"
  }
}

Extract a Top-Level Field

SELECT details ->> 'name'
FROM customers;

Result:

John

Extract a Nested Field

SELECT details -> 'address' ->> 'city'
FROM customers;

Result:

Lagos

Understanding PostgreSQL Operators

Operator	Purpose
->	Returns JSON object
->>	Returns text value

Example:

details -> 'address'

returns JSON.

While:

details ->> 'name'

returns text.

Querying JSON in MySQL

MySQL provides JSON support through JSON functions.

Sample Table

CREATE TABLE customers (
    id INT,
    details JSON
);

Extract a Field

SELECT JSON_EXTRACT(
    details,
    '$.name'
)
FROM customers;

Extract Nested Fields

SELECT JSON_EXTRACT(
    details,
    '$.address.city'
)
FROM customers;

Result:

Lagos

Shorter Syntax

MySQL also supports:

SELECT details->'$.address.city'
FROM customers;

Return Plain Text

SELECT details->>'$.address.city'
FROM customers;

This removes JSON formatting and returns a standard string.

Querying JSON in SQL Server

SQL Server provides built-in JSON functions.

Example JSON

{
  "customer": {
    "name": "John",
    "city": "Lagos"
  }
}

Extract a Value

SELECT JSON_VALUE(
    details,
    '$.customer.name'
)
FROM customers;

Extract Nested Data

SELECT JSON_VALUE(
    details,
    '$.customer.city'
)
FROM customers;

Result:

Lagos

SQL Server’s JSON path syntax is very similar to MySQL.

Working with JSON Arrays

JSON often contains arrays.

Example:

{
  "products": [
    "Laptop",
    "Mouse",
    "Keyboard"
  ]
}

PostgreSQL

Access the first item:

SELECT details
       -> 'products'
       ->> 0
FROM orders;

Result:

Laptop

MySQL

SELECT JSON_EXTRACT(
    details,
    '$.products[0]'
)
FROM orders;

SQL Server

SELECT JSON_VALUE(
    details,
    '$.products[0]'
)
FROM orders;

Filtering Records Using Nested JSON

You can use JSON fields in WHERE clauses.

PostgreSQL

SELECT *
FROM customers
WHERE details
      -> 'address'
      ->> 'city' = 'Lagos';

MySQL

SELECT *
FROM customers
WHERE JSON_EXTRACT(
      details,
      '$.address.city'
      ) = 'Lagos';

SQL Server

SELECT *
FROM customers
WHERE JSON_VALUE(
      details,
      '$.address.city'
      ) = 'Lagos';

This allows analysts to filter data based on JSON values.

Flattening Nested JSON

In analytics projects, nested JSON often needs to be flattened.

Example:

{
  "customer": {
    "name": "John",
    "city": "Lagos"
  }
}

Desired output:

Name	City
John	Lagos

PostgreSQL

SELECT
    details -> 'customer'
            ->> 'name' AS name,
    details -> 'customer'
            ->> 'city' AS city
FROM customers;

This transforms nested JSON into a tabular format suitable for reporting.

JSON in Data Warehouses

Many cloud analytics platforms support JSON querying.

Examples include:

• Snowflake
• Google BigQuery
• Amazon Redshift
• Databricks

These platforms provide specialized functions for semi-structured data analysis.

As event-driven architectures grow, JSON querying becomes an increasingly important skill.

Common Use Cases

API Data Analysis

Extract fields from API responses stored in databases.

Event Tracking

Analyze user behavior events stored as JSON.

E-Commerce Analytics

Query customer and order attributes.

Log Analytics

Extract information from application logs.

Data Lake Processing

Work with semi-structured datasets efficiently.

Best Practices

Use Native JSON Data Types

Avoid storing JSON as plain text when possible.

Index Frequently Queried Fields

JSON indexing can significantly improve performance.

Flatten Data for Reporting

Business intelligence tools often perform better with tabular structures.

Validate JSON Structure

Inconsistent schemas can cause query failures.

Avoid Excessive Nesting

Deeply nested JSON becomes harder to query and maintain.

Common Challenges

Inconsistent Data Structures

Different records may contain different keys.

Performance Issues

Heavy JSON queries can be slower than traditional column queries.

Null Values

Missing keys often return NULL values.

Complex Arrays

Nested arrays may require advanced transformations.

Understanding these challenges helps build more reliable SQL workflows.

Real-World Example

Imagine an online store storing order events as JSON:

{
  "customer": {
    "id": 101,
    "country": "Nigeria"
  },
  "product": {
    "name": "Laptop"
  }
}

An analyst wants:

• Customer country
• Product name

Using JSON extraction functions, these values can be retrieved directly without restructuring the original data.

This flexibility is one reason JSON has become so popular in modern data architectures.

Querying nested JSON fields is an essential skill for modern data analysts and data engineers. As APIs, event streams, and cloud platforms increasingly rely on semi-structured data, understanding how to extract values from nested JSON structures becomes critical.

Whether you’re using PostgreSQL, MySQL, SQL Server, or cloud data warehouses, JSON query functions allow you to access, filter, and transform nested data efficiently.

By mastering these techniques, you’ll be better prepared to work with real-world analytics datasets where structured and semi-structured data often coexist.

FAQ