Modern applications rarely store all their data in simple relational columns.

APIs, event streams, application logs, configuration systems, and modern data platforms frequently produce JSON.

A single JSON record might contain:

{
  "customer": {
    "id": 1042,
    "name": "Sarah"
  },
  "orders": [
    {
      "id": 501,
      "amount": 120
    },
    {
      "id": 502,
      "amount": 85
    }
  ]
}

Traditional SQL works naturally with rows and columns, but JSON introduces another layer of structure.

You may need to:

• Extract values from nested objects
• Search inside JSON arrays
• Expand arrays into rows
• Filter records based on nested fields
• Aggregate values from JSON
• Handle missing properties
• Convert JSON values into SQL types

The exact syntax varies between database systems, but the underlying concepts are remarkably similar.

To query JSON arrays and nested objects in SQL, you generally need to perform four operations:

JSON Document
     ↓
Navigate
     ↓
Extract
     ↓
Expand Arrays
     ↓
Filter / Aggregate

For example, a nested object such as:

{
  "customer": {
    "name": "Sarah"
  }
}

can often be accessed conceptually as:

customer → name

while an array such as:

{
  "tags": ["python", "sql", "data"]
}

needs to be unnested, flattened, or otherwise expanded before you can treat each element as a normal SQL row.

Why JSON Is Common in Data Engineering

JSON is widely used because it is flexible and easy for applications to exchange.

Common sources include:

• REST APIs
• Webhooks
• Application events
• SaaS platforms
• NoSQL databases
• Log systems
• Event streaming platforms
• Configuration files

For example, an event pipeline might receive:

{
  "event": "purchase",
  "user": {
    "id": 123,
    "country": "US"
  },
  "products": [
    {
      "sku": "A100",
      "quantity": 2
    },
    {
      "sku": "B200",
      "quantity": 1
    }
  ]
}

Instead of immediately transforming every nested field into separate tables, a data platform may temporarily store the JSON document.

SQL can then be used to analyze it.

JSON Objects vs JSON Arrays

Understanding the difference is essential.

JSON Object

An object contains key-value pairs.

{
  "name": "Sarah",
  "age": 31,
  "country": "US"
}

You access properties by their keys:

name
age
country

JSON Array

An array contains an ordered collection of values.

[
  "Python",
  "SQL",
  "Power BI"
]

Arrays can also contain objects:

[
  {
    "id": 1,
    "price": 50
  },
  {
    "id": 2,
    "price": 75
  }
]

Querying arrays usually requires an additional operation to turn their elements into rows.

A Sample JSON Dataset

Consider a table called:

customers

with columns:

customer_id
customer_data

where customer_data contains:

{
  "name": "Sarah",
  "country": "US",
  "preferences": {
    "language": "English",
    "currency": "USD"
  },
  "orders": [
    {
      "order_id": 101,
      "amount": 120
    },
    {
      "order_id": 102,
      "amount": 85
    }
  ]
}

There are three different levels here:

customer_data
   ↓
preferences
   ↓
language

and:

customer_data
   ↓
orders
   ↓
array elements
   ↓
order_id / amount

These require slightly different querying techniques.

Querying Nested JSON Objects

The first task is often extracting a value from a nested object.

Conceptually:

customer_data → preferences → language

Different databases provide different operators.

For example, PostgreSQL commonly uses:

SELECT
    customer_data -> 'preferences' ->> 'language' AS language
FROM customers;

The important distinction is:

->   returns JSON
->>  returns text

So:

customer_data -> 'preferences'

returns a JSON object, while:

customer_data -> 'preferences' ->> 'language'

returns the value as text.

PostgreSQL JSON Operators

PostgreSQL provides several useful JSON operators.

Operator	Purpose
->	Get JSON object/array element
->>	Get JSON value as text
#>	Get nested JSON object/array
#>>	Get nested value as text

For example:

SELECT
    customer_data ->> 'name' AS customer_name
FROM customers;

For deeper nesting:

SELECT
    customer_data #>> '{preferences,language}' AS language
FROM customers;

Querying JSON in MySQL

MySQL provides functions such as:

JSON_EXTRACT()

For example:

SELECT
    JSON_EXTRACT(customer_data, '$.name') AS customer_name
FROM customers;

You can navigate deeper:

SELECT
    JSON_EXTRACT(
        customer_data,
        '$.preferences.language'
    ) AS language
FROM customers;

MySQL also supports the shorthand -> and ->> operators for JSON columns.

For example:

SELECT
    customer_data->>'$.name' AS customer_name
FROM customers;

JSON Path Expressions

Many SQL systems use JSONPath-style expressions to navigate JSON.

Consider:

{
  "customer": {
    "name": "Sarah"
  }
}

The path is:

$.customer.name

The $ represents the root JSON document.

For example:

$
└── customer
    └── name

JSON paths become particularly useful when working with arrays.

Querying JSON Arrays

Consider:

{
  "tags": [
    "python",
    "sql",
    "analytics"
  ]
}

The array exists at:

$.tags

Individual elements can be accessed using indexes.

For example:

$.tags[0]

returns:

python

while:

$.tags[1]

returns:

sql

But indexing isn’t always the best approach.

If you want to analyze every array element, you generally need to expand the array.

Why You Should Flatten Arrays

Suppose one customer has:

["python", "sql", "analytics"]

and another has:

["sql", "python"]

If these remain inside JSON arrays, counting how many customers use SQL becomes more complicated.

After flattening:

customer_id	skill
1	python
1	sql
1	analytics
2	sql
2	python

Now ordinary SQL becomes possible:

SELECT
    skill,
    COUNT(*) AS customers
FROM customer_skills
GROUP BY skill;

This is one of the most important concepts when querying JSON arrays.

PostgreSQL: jsonb_array_elements()

PostgreSQL provides:

jsonb_array_elements()

to expand a JSON array into rows.

Suppose:

{
  "orders": [
    {"id": 101, "amount": 120},
    {"id": 102, "amount": 85}
  ]
}

You can expand the array:

SELECT
    customer_id,
    jsonb_array_elements(customer_data->'orders') AS order_data
FROM customers;

The result conceptually becomes:

customer_id	order_data
1	{"id":101,"amount":120}
1	{"id":102,"amount":85}

You can then extract fields from each object.

Extracting Fields From Array Objects

You can combine array expansion with JSON operators.

SELECT
    c.customer_id,
    order_data ->> 'id' AS order_id,
    (order_data ->> 'amount')::numeric AS amount
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements(c.customer_data->'orders') AS order_data;

Now each order becomes a regular SQL row.

This pattern is extremely useful for event and API data.

PostgreSQL jsonb_array_elements_text()

If the array contains simple scalar values:

{
  "tags": [
    "python",
    "sql",
    "analytics"
  ]
}

you can use:

jsonb_array_elements_text()

For example:

SELECT
    customer_id,
    tag
FROM customers
CROSS JOIN LATERAL
    jsonb_array_elements_text(customer_data->'tags') AS tag;

This produces one row per tag.

BigQuery: UNNEST()

BigQuery uses a different approach.

Suppose you have an array field called:

orders

You can use:

SELECT
    customer_id,
    order
FROM `project.dataset.customers`,
UNNEST(orders) AS order;

UNNEST() converts an array into rows.

This is one of the most important BigQuery techniques for nested and repeated data.

BigQuery Nested Fields

BigQuery can store nested and repeated fields directly.

For example:

customer
 ├── id
 ├── name
 └── orders[]
      ├── order_id
      └── amount

You can access nested fields using dot notation:

SELECT
    customer.id,
    customer.name
FROM `project.dataset.customers`;

For repeated fields:

SELECT
    customer.id,
    order.order_id,
    order.amount
FROM `project.dataset.customers`,
UNNEST(customer.orders) AS order;

Snowflake: FLATTEN()

Snowflake provides the:

FLATTEN()

table function for expanding arrays and objects.

For example:

SELECT
    customer_id,
    value
FROM customers,
LATERAL FLATTEN(
    input => customer_data:orders
);

You can then extract fields from value.

For example:

SELECT
    customer_id,
    value:id::INT AS order_id,
    value:amount::NUMBER AS amount
FROM customers,
LATERAL FLATTEN(
    input => customer_data:orders
);

DuckDB and JSON

DuckDB is particularly useful for local analytics involving JSON.

It provides JSON functions and operators that allow you to extract nested values and work with JSON arrays.

For example:

SELECT
    json_extract(data, '$.customer.name') AS customer_name
FROM events;

DuckDB can also transform JSON into relational structures, making it useful for analyzing API responses and local files without setting up a large database infrastructure.

Filtering Nested JSON Values

Extracting values is only the beginning.

You may want to filter rows.

Suppose you want customers from the United States.

In PostgreSQL:

SELECT *
FROM customers
WHERE customer_data ->> 'country' = 'US';

For nested values:

SELECT *
FROM customers
WHERE customer_data #>> '{preferences,language}'
    = 'English';

The general pattern is:

Extract JSON value
       ↓
Compare with condition
       ↓
Filter rows

Filtering JSON Arrays

Suppose you want customers whose tags contain:

sql

In PostgreSQL, one approach is:

SELECT *
FROM customers
WHERE customer_data->'tags'
    ? 'sql';

This is useful when the JSON value is a JSONB array of strings.

For more complex conditions, you can expand the array:

SELECT DISTINCT
    customer_id
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements_text(
        customer_data->'tags'
    ) AS tag
WHERE tag = 'sql';

Filtering Objects Inside Arrays

This becomes more interesting when arrays contain objects.

Suppose:

{
  "orders": [
    {
      "id": 101,
      "status": "completed",
      "amount": 120
    },
    {
      "id": 102,
      "status": "cancelled",
      "amount": 85
    }
  ]
}

You may want customers with orders above $100.

After expanding the array:

SELECT DISTINCT
    c.customer_id
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements(
        customer_data->'orders'
    ) AS order_data
WHERE (order_data->>'amount')::numeric > 100;

The important technique is:

JSON array
    ↓
Expand
    ↓
Extract field
    ↓
Cast to correct type
    ↓
Filter

Casting JSON Values

JSON values often need to be converted to SQL data types.

Suppose:

{
  "amount": 120
}

You might initially extract:

order_data ->> 'amount'

which produces text.

You can convert it:

(order_data ->> 'amount')::numeric

Similarly:

(order_data ->> 'id')::integer

or:

(order_data ->> 'created_at')::timestamp

Correct data types matter for:

• Arithmetic
• Comparisons
• Sorting
• Aggregation
• Date operations

Aggregating JSON Data

Once JSON arrays are flattened, ordinary SQL aggregation becomes available.

For example:

SELECT
    c.customer_id,
    SUM(
        (order_data ->> 'amount')::numeric
    ) AS total_spend
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements(
        customer_data->'orders'
    ) AS order_data
GROUP BY c.customer_id;

This converts nested order data into a customer-level metric.

Counting Array Elements

You don’t always need to flatten an array.

If you only need its length, database-specific JSON functions can often calculate it directly.

In PostgreSQL:

SELECT
    customer_id,
    jsonb_array_length(
        customer_data->'orders'
    ) AS order_count
FROM customers;

This is more efficient and simpler when you don’t need to inspect individual elements.

Handling Missing JSON Fields

Real-world JSON is often inconsistent.

One record might contain:

{
  "customer": {
    "name": "Sarah"
  }
}

while another contains:

{
  "customer": {
    "name": "John",
    "country": "US"
  }
}

The country field is missing from the first record.

Your SQL should therefore account for:

• Missing properties
• Null values
• Empty arrays
• Unexpected types

Don’t assume every JSON document has the same structure.

NULL vs Missing Values

A JSON property can be:

{
  "country": null
}

or completely absent:

{
  "name": "Sarah"
}

These aren’t always semantically identical.

Your database’s JSON functions may handle these cases differently, so test the behavior before building production logic around it.

JSON Arrays of Scalars vs Objects

There are two major patterns.

Array of Scalars

{
  "tags": [
    "python",
    "sql",
    "data"
  ]
}

Use an array-expansion function to produce:

python
sql
data

Array of Objects

{
  "orders": [
    {
      "id": 1,
      "amount": 50
    },
    {
      "id": 2,
      "amount": 75
    }
  ]
}

Expand first, then extract individual properties.

orders[]
   ↓
order object
   ↓
id / amount

JSON Arrays Nested Inside Objects

Real data can have several levels of nesting.

For example:

{
  "customer": {
    "profile": {
      "country": "US"
    },
    "orders": [
      {
        "items": [
          {
            "sku": "A100",
            "quantity": 2
          }
        ]
      }
    ]
  }
}

Now there are multiple levels:

customer
  ↓
orders[]
  ↓
items[]
  ↓
sku

The general strategy remains:

Navigate
   ↓
Expand first array
   ↓
Expand nested array
   ↓
Extract values

Querying Multiple Nested Arrays

Suppose each order contains multiple items.

In PostgreSQL, you might use:

SELECT
    c.customer_id,
    order_data ->> 'id' AS order_id,
    item_data ->> 'sku' AS sku,
    (item_data ->> 'quantity')::integer AS quantity
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements(
        customer_data->'orders'
    ) AS order_data
CROSS JOIN LATERAL
    jsonb_array_elements(
        order_data->'items'
    ) AS item_data;

This creates a relational representation:

customer_id	order_id	sku	quantity
1	101	A100	2
1	101	B200	1
1	102	C300	4

JSON Querying and SQL Joins

Once nested data is expanded, you can join it to normal relational tables.

For example:

SELECT
    c.customer_id,
    o.order_id,
    p.product_name,
    o.amount
FROM customers c
CROSS JOIN LATERAL
    jsonb_array_elements(
        customer_data->'orders'
    ) AS order_data
JOIN products p
    ON p.product_id =
       (order_data->>'product_id')::integer;

This is a powerful pattern for integrating semi-structured and relational data.

When Should JSON Stay Nested?

JSON isn’t automatically bad database design.

Keeping data nested can make sense when:

• The structure changes frequently
• The data is consumed as a document
• The nested structure is rarely queried
• The source system already produces JSON
• The data is primarily used for archival purposes

But frequently queried business attributes may benefit from being extracted into regular columns.

When Should You Flatten JSON?

Flattening is often useful when:

• Analysts query the fields frequently
• You need joins
• You frequently aggregate values
• You need strong type enforcement
• The schema is relatively stable
• Query performance matters

A common architecture is:

Raw JSON
   ↓
Bronze / Raw Layer
   ↓
Flattened Transformation
   ↓
Analytics Model

This allows you to preserve the original data while creating efficient structures for analysis.

JSON in Data Lakes and Warehouses

JSON is particularly common in modern data platforms.

A pipeline may look like:

API
 ↓
JSON
 ↓
Object Storage
 ↓
Data Warehouse / Lakehouse
 ↓
SQL Transformation
 ↓
Analytics Table

The raw JSON provides flexibility.

The transformed relational layer provides usability.

Performance Considerations

JSON querying can become expensive when applied to very large datasets.

For example:

SELECT *
FROM events
WHERE JSON_EXTRACT(...)

may require the database to repeatedly parse or navigate JSON documents.

For frequently queried fields, consider:

• Extracting fields into columns
• Materializing transformations
• Creating appropriate indexes
• Using generated columns
• Partitioning
• Clustering
• Choosing native nested types where supported

Indexing JSON

Some databases support specialized JSON indexes.

PostgreSQL, for example, supports indexes on jsonb.

A GIN index can improve certain containment and key-existence queries:

CREATE INDEX idx_customer_data
ON customers
USING GIN (customer_data);

However, indexing strategy should be based on actual query patterns.

Don’t add JSON indexes blindly.

SQL JSON Functions Are Database-Specific

One of the biggest challenges is portability.

The same operation may look different across databases.

Task	PostgreSQL	BigQuery	Snowflake	MySQL
Extract JSON	->, ->>	JSON_VALUE / field access	:	JSON_EXTRACT()
Expand array	jsonb_array_elements()	UNNEST()	FLATTEN()	JSON table/functions
Array length	jsonb_array_length()	ARRAY_LENGTH()	ARRAY_SIZE()	JSON_LENGTH()
JSON path	Operators / path	JSONPath	JSON path	JSON path

The exact implementation depends on the database.

The concepts, however, remain similar.

A General JSON Querying Pattern

Across database systems, the workflow is usually:

1. Locate JSON document
        ↓
2. Navigate to property
        ↓
3. Extract value
        ↓
4. Expand arrays if necessary
        ↓
5. Convert data types
        ↓
6. Filter
        ↓
7. Aggregate
        ↓
8. Join with relational data

Once you understand this pattern, switching between databases becomes much easier.

Common Mistakes

Treating JSON Like Normal Columns

A nested JSON field isn’t automatically a relational column.

You need to navigate its structure.

Forgetting to Flatten Arrays

Trying to aggregate an entire array as if it were a scalar often produces incorrect results.

Ignoring Data Types

Extracted JSON values may be strings.

Always cast them appropriately before calculations.

Assuming Every Record Has the Same Schema

Production JSON frequently contains missing or optional properties.

Expanding Arrays Unnecessarily

If you only need the array length, don’t create one row per element.

Ignoring Performance

Repeated JSON parsing across billions of records can become expensive.

Using Database-Specific Syntax Without Checking

PostgreSQL, BigQuery, Snowflake, and MySQL use different JSON functions.

Best Practices

Keep Raw JSON When Useful

Preserving the original payload can make debugging and reprocessing easier.

Create Structured Models for Frequent Queries

Frequently accessed fields should generally be made easy to query.

Flatten Arrays Deliberately

Only expand arrays when the analysis requires element-level data.

Cast Values Explicitly

Convert JSON strings to numeric, date, Boolean, or other appropriate SQL types.

Handle Missing Fields

Production data is rarely perfectly uniform.

Monitor Schema Changes

API changes can silently alter JSON structures.

Test With Real Data

JSON structures often contain edge cases that aren’t obvious from documentation.

Optimize High-Volume Queries

Use indexes, materialized transformations, partitioning, clustering, or native nested data types where appropriate.

Conclusion

JSON gives modern data systems flexibility, but it also introduces complexity when you need to analyze nested information.

The most important concepts are:

Navigate nested objects → extract values → expand arrays → cast types → filter → aggregate.

The exact syntax changes between PostgreSQL, MySQL, BigQuery, Snowflake, DuckDB, and other databases.

But the underlying mental model remains the same.

If you frequently work with APIs, event data, logs, or semi-structured data, understanding how SQL interacts with JSON arrays and nested objects is an essential data engineering skill.

The goal isn’t to keep everything in JSON forever.

Instead, a strong data architecture often preserves raw JSON for flexibility while transforming frequently used information into reliable, typed, query-friendly structures for analytics.

Frequently Asked Questions