SQL JSON: working with nested and semi-structured data

When one column contains a structure

Relational tables usually store one value in each column. But data arriving from APIs, event streams, external applications, and document-oriented systems often has a nested structure. An order event might look like this:

{
  "channel": "web",
  "payment": {
    "method": "card",
    "amount": 1500
  },
  "shipping": {
    "city": "Bengaluru",
    "priority": true
  },
  "tags": ["new_customer", "promo"]
}

Instead of creating a separate relational column for every possible attribute, a database may store this structure in a JSON column. Consider the following table, where metadata is stored as PostgreSQL jsonb:

JSON allows one column to contain objects, nested objects, arrays, strings, numbers, booleans, and null values. The challenge is that SQL still needs a way to reach those values and use them in filters, calculations, and reports.

Extracting a JSON field with ->

Suppose we want the channel field. PostgreSQL provides the -> operator:

SELECT
    order_id,
    metadata -> 'channel' AS channel
FROM order_events
ORDER BY order_id;

Notice the quotation marks around "web". That is because metadata -> 'channel' returns a JSON value, not ordinary SQL text. PostgreSQL’s -> operator preserves the JSON or jsonb type of the extracted value.

Extracting a field as text with ->>

Most SQL operations need the scalar value rather than its JSON representation. Use ->> to return the value as text:

SELECT
    order_id,
    metadata ->> 'channel' AS channel
FROM order_events
ORDER BY order_id;

->    returns JSON
->>   returns text

PostgreSQL documents ->> as extracting an object field or array element as SQL text. A useful rule: use -> when you want to keep working with a JSON structure, and ->> when you want the extracted scalar value as text.

Filtering on an extracted value

Once we extract text, we can use familiar SQL conditions. Return orders placed through the mobile channel:

SELECT
    order_id,
    customer,
    metadata ->> 'channel' AS channel
FROM order_events
WHERE metadata ->> 'channel' = 'mobile'
ORDER BY order_id;

The JSON extraction becomes part of an ordinary SQL comparison: metadata ->> 'channel' = 'mobile'.

Extracting nested values

JSON often contains objects inside other objects. Our payment field looks like {"method":"card","amount":1500}. To reach method, we move through the structure step by step with metadata -> 'payment' ->> 'method'. The first operation metadata -> 'payment' returns the nested JSON object, and then ->> 'method' extracts card:

SELECT
    order_id,
    customer,
    metadata -> 'payment' ->> 'method' AS payment_method
FROM order_events
ORDER BY order_id;

Extracting nested values with a path

PostgreSQL also supports path operators. metadata #>> '{payment,method}' means metadata then payment then method, and returns the final value as text, producing the same scalar values. The related operators are:

#>    extract JSON at a path
#>>   extract text at a path

PostgreSQL supports object keys and array indexes within these paths. For shallow structures, chained operators are often easy to read (metadata -> 'payment' ->> 'method'). For deeper structures, a path can be cleaner (metadata #>> '{payment,method}').

The JSON number that is being compared as text

Consider the payment amount {"amount":1500}. Suppose the requirement is return orders with payment amounts greater than 1,000. Someone writes the query below. It looks reasonable, but #>> returns text, so values such as 1500, 3200, 3600, 8500, 900 are being treated as character strings rather than numbers.

SELECT
    order_id,
    customer,
    metadata #>> '{payment,amount}' AS payment_amount
FROM order_events
WHERE metadata #>> '{payment,amount}' > '1000'
ORDER BY order_id;

The fix: extract, then CAST

The business rule is numeric, so convert the extracted value to a numeric type:

SELECT
    order_id,
    customer,
    CAST(
        metadata #>> '{payment,amount}'
        AS DECIMAL(10, 2)
    ) AS payment_amount
FROM order_events
WHERE CAST(
        metadata #>> '{payment,amount}'
        AS DECIMAL(10, 2)
      ) > 1000
ORDER BY payment_amount;

Esha’s 900 is correctly excluded.

JSON values still have types

Inside JSON, these values are different:

2       JSON number
"2"     JSON string
true    JSON boolean
null    JSON null

That distinction matters. If your application stores numeric information as "amount": "1500" instead of "amount": 1500, the JSON itself already contains a string rather than a number. Consistent JSON schemas make downstream SQL easier to reason about. PostgreSQL provides functions such as jsonb_typeof to inspect the top-level JSON type of a value:

SELECT
    order_id,
    jsonb_typeof(metadata -> 'payment' -> 'amount') AS amount_type
FROM order_events;

For our sample data, the result is number.

Missing keys and nested values

Look at Esha’s JSON: there is no shipping field. Now run metadata #>> '{shipping,city}'. For Esha, the result is NULL. PostgreSQL’s JSON extraction operators return SQL NULL rather than raising an error when the requested structure or key is not present. That means familiar NULL logic becomes relevant again. To find rows without an available shipping city:

SELECT
    order_id,
    customer
FROM order_events
WHERE metadata #>> '{shipping,city}' IS NULL;

Missing key and JSON null need care

Dev’s JSON contains "shipping": null. Esha’s JSON does not contain the shipping key at all. These represent different structures in the JSON document: for Dev, shipping exists with JSON null; for Esha, the shipping key is absent. Depending on how you extract and interpret them, both can eventually appear as SQL NULL.

If the distinction matters to the business, test for key existence or inspect the JSON structure rather than assuming all SQL NULL results mean the same thing. With PostgreSQL jsonb, the ? operator can test whether a top-level key exists:

SELECT
    order_id,
    metadata ? 'shipping' AS has_shipping_key
FROM order_events;

This can distinguish “the key is absent” from “the key exists but contains a null-like value.”

Working with JSON arrays

JSON can also contain arrays. Our tags field might contain ["new_customer","promo"] or ["promo","express"]. To extract the array itself, we use -> rather than ->> because we want to preserve the array as JSON:

SELECT
    order_id,
    metadata -> 'tags' AS tags
FROM order_events;

Extracting one array element

PostgreSQL JSON arrays use zero-based indexes for the extraction operator, so metadata -> 'tags' ->> 0 returns the first tag:

SELECT
    order_id,
    metadata -> 'tags' ->> 0 AS first_tag
FROM order_events
ORDER BY order_id;

Order 1004 has an empty array, so there is no element at index 0.

Counting array elements

PostgreSQL provides jsonb_array_length(...) to return the number of elements in a top-level JSON array:

SELECT
    order_id,
    jsonb_array_length(metadata -> 'tags') AS tag_count
FROM order_events
ORDER BY order_id;

This is useful when the number of array members matters without needing to inspect each one individually.

Expanding an array into rows

Sometimes the requirement is return one row for every order-tag combination. For order 1001 with ["new_customer","promo"], we want two rows. PostgreSQL provides jsonb_array_elements_text(...) to expand a JSON array into a set of text values:

SELECT
    o.order_id,
    o.customer,
    tag
FROM order_events AS o
CROSS JOIN LATERAL
    jsonb_array_elements_text(o.metadata -> 'tags') AS tag
ORDER BY
    o.order_id,
    tag;

Order 1004 has no tags, so there is no array element to expand.

Filtering JSON arrays

Suppose we want orders whose tags contain promo. PostgreSQL jsonb provides containment and existence operators. For a top-level array, one possible test is metadata -> 'tags' ? 'promo':

SELECT
    order_id,
    customer,
    metadata -> 'tags' AS tags
FROM order_events
WHERE metadata -> 'tags' ? 'promo'
ORDER BY order_id;

The exact JSON query syntax varies considerably across databases, so array-search expressions should follow the database you are using. The broader concept remains: decide whether you need one array element, the entire array, or a test for whether an element exists.

Casting nested JSON values

JSON frequently contains values that need to participate in ordinary SQL calculations. Extracting metadata #>> '{payment,amount}' returns text such as 3200. For aggregation, cast it:

SELECT
    SUM(
        CAST(
            metadata #>> '{payment,amount}'
            AS DECIMAL(10, 2)
        )
    ) AS total_payment_amount
FROM order_events;

The same principle applies to other data types, and the correct target type comes from what the value represents:

Numeric     CAST(metadata ->> 'quantity'          AS INTEGER)
Decimal     CAST(metadata #>> '{payment,amount}'  AS DECIMAL(10, 2))
Date        CAST(metadata ->> 'order_date'        AS DATE)
Timestamp   CAST(metadata ->> 'created_at'        AS TIMESTAMP)

Invalid JSON values can still break CAST

Suppose most documents contain "amount": 1500 but one contains "amount": "unknown". The extraction metadata #>> '{payment,amount}' can produce unknown, and casting it to DECIMAL(10, 2) can fail because unknown is not a valid decimal representation. JSON gives data flexibility, but it does not guarantee that every document follows the same schema. This is one of the main production challenges with semi-structured data.

Before casting JSON values, ask:

If the data contract says payment.amount must always be numeric, then invalid documents should ideally be rejected or corrected during ingestion rather than handled differently in every downstream query.

JSON flexibility does not remove the need for a schema

One advantage of JSON is that different documents can contain different fields, which is useful for optional or evolving attributes. But a JSON column should not automatically become a place where every field is stored without rules. If downstream queries repeatedly depend on payment.amount, customer_id, ordered_at, or status, those fields may be better represented as ordinary typed relational columns. JSON is particularly useful for optional attributes, evolving event payloads, external API responses, sparse metadata, nested structures, and arrays. Frequently queried core business fields often benefit from explicit relational columns with well-defined SQL types.

How to spot JSON problems

Look carefully at JSON logic when:

When something behaves unexpectedly, inspect both what the JSON structure is and what SQL type the extraction expression returns.

Ask five questions when working with JSON

One more prediction

Consider a document whose payment.amount is 900. Requirement: return rows where payment.amount is numerically greater than 1,000. Which condition best expresses the requirement in PostgreSQL?

-- Option A
WHERE metadata #>> '{payment,amount}' > '1000'

-- Option B
WHERE CAST(
    metadata #>> '{payment,amount}'
    AS DECIMAL(10, 2)
) > 1000

-- Option C
WHERE metadata -> 'payment' -> 'amount' = '1000'

-- Option D
WHERE metadata ->> 'payment' > '1000'

Summary

JSON allows relational tables to store nested and semi-structured information. In PostgreSQL, use metadata -> 'channel' when you want the field as JSON and metadata ->> 'channel' when you want it as text. For nested values, metadata -> 'payment' ->> 'method' or metadata #>> '{payment,method}' can reach deeper into the document. For arrays, metadata -> 'tags' ->> 0 extracts one element while jsonb_array_elements_text(metadata -> 'tags') can expand all elements into rows.

Next, we will move into analytical SQL and learn how window functions calculate across related rows without collapsing them into one row per group.