Working with JSON in SQL

So far, we have worked with SQL tables where information is stored in separate columns, such as order_id, amount, and status.

Real applications do not always store data this way. Some information is stored as JSON, which allows related fields and lists to be kept together in a single column.

For example, an order may contain its payment details, shipping information, and tags as JSON. To use this information in SQL, we need to understand how it is organised and how to read individual values.

In this lesson, you will learn how to query JSON fields and arrays, handle missing or unexpected values, and work with documents whose structure has changed over time.

Sample data

We will continue using the shop data from the previous lessons.

A new table, order_events, stores checkout events for orders 1001–1007. Each row has a metadata column of type JSON, containing details such as payment method, shipping information, and tags.

The examples will highlight differences between these documents as needed.

The full metadata documents are part of the sample dataset. We will examine their JSON values throughout the lesson rather than displaying all seven large documents in one table.

In a regular SQL table, a column usually stores a single value, such as a customer name, order amount, or date. JSON allows several related values to be stored together, including lists and other groups of values.

Let’s look at the JSON stored for order 1001:

SELECT order_id, metadata
FROM order_events
WHERE order_id = 1001;

The metadata column contains:

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

How is JSON organised?

JSON is a text format for representing structured information. The document above is a JSON object. An object contains keys and values enclosed in curly braces {}.

Each key identifies a piece of information.

For example:

• "channel" has the value "web".
• "coupon" has the value "WELCOME10".
• "payment" contains another object with payment details.
• "tags" contains a list of values.

JSON supports six types of values:

Two types are especially important:

Objects group related information using keys and values. For example, the payment object contains the payment method and amount.

Arrays store lists of values in a specific order. For example, the tags array contains "new_customer" followed by "promo".

An object can contain another object or an array, allowing JSON to represent information at several levels.

Why do applications use JSON?

In a relational database, we might store payment and shipping details in separate columns and order tags in another table. With JSON, an application can keep these details in one document. It can also add a new field without adding a new column to the SQL table. This flexibility is why JSON is called semi-structured data.

Each document has an internal structure, but different documents do not need to contain exactly the same fields. For example, one order may contain a coupon field, while another may not.

How does SQL validate JSON?

DuckDB provides a JSON data type that checks whether stored values follow valid JSON syntax. We can examine both the SQL column type and the JSON value using these functions:

SELECT
    typeof(metadata)               AS column_type,
    json_type(metadata)            AS document_type,
    json_valid('{"channel":"web"')  AS broken_is_valid
FROM order_events
WHERE order_id = 1001;

Each function checks something different:

• typeof() returns the SQL data type of the column. Here, it is JSON.
• json_type() returns the type of the JSON value stored inside it. Here, it is an OBJECT.
• json_valid() checks whether the supplied text contains valid JSON syntax.

In the last expression, the JSON text is missing a closing curly brace }. Therefore, json_valid() returns false. Trying to store that malformed document in a DuckDB JSON column would raise an error.

JSON syntax rules

JSON requires double quotes around object keys and string values. This is valid JSON:

{"channel": "web"}

But this is not:

{'channel': 'web'}

When writing JSON directly inside SQL, we normally use single quotes around the complete JSON text:

SELECT json_valid('{"channel": "web"}');

The outer single quotes belong to SQL. The inner double quotes belong to JSON.

Now that we understand how JSON is structured, let’s learn how to retrieve individual values using SQL. Suppose the support team wants to know which channel each order came from, such as web, mobile, or store.

The channel is stored inside metadata under the key channel. To read it, we need to tell SQL where to find the value.

Understanding JSON paths

A JSON path identifies the location of a value inside a JSON document. We will use JSONPath notation:

For example: metadata ->> '$.payment.amount'

The path means: start at the document, find payment, then read amount inside it. DuckDB also allows a simple top-level key name such as 'channel' without $. We will use explicit paths throughout the lesson to make the structure easier to follow.

Two ways to extract a value

DuckDB provides two operators for reading JSON:

• -> extracts the value as JSON.
• ->> extracts the value as SQL text.

The difference becomes important when the extracted value is a JSON string.

SELECT
    order_id,
    metadata -> '$.channel' AS channel
FROM order_events
WHERE order_id IN (1001, 1002)
ORDER BY order_id;

Let’s compare both operators:

SELECT
    order_id,
    metadata -> '$.channel'          AS as_json,
    metadata ->> '$.channel'         AS as_text,
    typeof(metadata -> '$.channel')  AS json_side,
    typeof(metadata ->> '$.channel') AS text_side
FROM order_events
WHERE order_id IN (1001, 1002)
ORDER BY order_id;

Notice two differences:

1. -> returns JSON, so strings retain their JSON quotation marks.
2. ->> returns VARCHAR, which is SQL text without the JSON quotation marks.

These operators are shorthand for DuckDB functions:

Which operator should you use?

• Use -> when you need to keep the extracted value as JSON, such as when working with nested objects or arrays.
• Use ->> when you need ordinary text for a report, a text comparison, or a type conversion.

For example, the payment field contains an object rather than a simple string. If we extract it using ->>, DuckDB returns the JSON representation as SQL text:

SELECT
    order_id,
    metadata ->> '$.payment' AS payment
FROM order_events
WHERE order_id = 1001;

The result looks like JSON, but its SQL type is text.

Finance wants to find orders worth more than $1,000.

Most documents store the order amount inside the payment object:

"payment": {
  "method": "card",
  "amount": 1500
}

We know how to extract this value:

metadata ->> '$.payment.amount'

But there is an important detail: ->> returns text, even when the original JSON value is a number.

First attempt: Compare with a number

An analyst writes:

SELECT
    order_id,
    metadata ->> '$.payment.amount' AS amount
FROM order_events
WHERE (metadata ->> '$.payment.amount') > 1000
ORDER BY order_id;

Result: An error.

DuckDB cannot directly compare the extracted VARCHAR value with an integer literal using this expression. The amount may represent a number, but after extraction with ->>, its SQL type is text.

Second attempt: Compare with text

The analyst changes the threshold to '1000':

SELECT
    order_id,
    metadata ->> '$.payment.amount' AS amount
FROM order_events
WHERE (metadata ->> '$.payment.amount') > '1000'
ORDER BY order_id;

The query now runs, but the result is incorrect. Order 1005 is worth only $750, yet it appears among orders worth more than $1,000. Why does $750 pass the filter?

Both sides of the comparison are now text. SQL compares text character by character rather than treating the values as numbers. This is the same problem we explored with text comparisons in P13.

The issue is not simply that order 1005 stores "750.00" with quotation marks. Even a JSON number such as 750 becomes text when extracted using ->>.

The fix: Convert text to a numeric type

To compare amounts correctly, convert the extracted value to a numeric SQL type first. For monetary amounts, we can use DECIMAL(10,2).

SELECT
    order_id,
    CAST(
        metadata ->> '$.payment.amount'
        AS DECIMAL(10,2)
    ) AS amount
FROM order_events
WHERE CAST(
    metadata ->> '$.payment.amount'
    AS DECIMAL(10,2)
) > 1000
ORDER BY order_id;

Now the comparison uses numeric values. Order 1005 is correctly excluded because $750 is less than $1,000.

Why is order 1006 missing?

Order 1006 has an amount of $3,600, but it does not appear in the result. To understand why, compare how the two application versions store payment information.

Order 1001 – Newer JSON structure

{
  "payment": {
    "method": "card",
    "amount": 1500
  }
}

Order 1006 – Older JSON structure

{
  "app_version": 1,
  "channel": "web",
  "amount": 3600,
  "payment_method": "card",
  "ship_to": "London"
}

In the newer structure, amount is inside the payment object. In the older structure, it is stored directly at the top level.

Our query uses: metadata ->> '$.payment.amount'

This path works for order 1001, but not for order 1006 because its document has no payment object. As a result, SQL returns NULL for order 1006. When the WHERE clause compares this value with 1000, the condition evaluates to UNKNOWN, so the order is excluded.

We will learn how to handle different JSON structures in section 10.

A SQL column can have the type JSON, but the values inside its documents may have different JSON types. For example, one field might contain a number in one document and a string in another.

DuckDB provides json_type() to inspect the type of a JSON value.

SELECT
    json_type(CAST('"web"' AS JSON)) AS string,
    json_type(CAST('1500' AS JSON))  AS whole_number,
    json_type(CAST('15.5' AS JSON))  AS fraction,
    json_type(CAST('true' AS JSON))  AS true_false,
    json_type(CAST('null' AS JSON))  AS json_null,
    json_type(CAST('["a"]' AS JSON)) AS array;

Here, json_type() identifies the internal JSON value type. DuckDB reports the positive integer 1500 as UBIGINT, an unsigned integer type. Negative whole numbers within the signed integer range can be reported as BIGINT. The fractional value is reported as DOUBLE.

The value shown as NULL under json_null is the type name returned by json_type(), not an SQL NULL result. We will examine this distinction in section 06.

When the same field has different types

Look at the amounts stored in our JSON documents. Most orders store payment.amount as a JSON number: "amount": 1500

But order 1005 stores it as a JSON string: "amount": "750.00"

Although both represent monetary amounts, their JSON types are different.

Finance wants to sum the amounts. An analyst tries converting the JSON values directly to integers.

SELECT
    SUM(
        CAST(metadata -> '$.payment.amount' AS INTEGER)
    ) AS total
FROM order_events;

Why does the conversion fail?

The problem is with order 1005, which stores its amount differently from the other orders. Most orders store the amount as a JSON number: "amount": 1500

But order 1005 stores it as a JSON string: "amount": "750.00"

Our query uses -> to extract the value as JSON and then tries to convert it directly to INTEGER. DuckDB can convert the JSON number 1500 to an integer. However, it cannot directly convert the JSON string "750.00" to INTEGER because the string contains a decimal-formatted number.

The fix is to extract the value as text and convert it to DECIMAL(10,2):

CAST(
    metadata ->> '$.payment.amount'
    AS DECIMAL(10,2)
)

This handles both formats:

• JSON number 1500 becomes 1500.00.
• JSON string "750.00" becomes 750.00.

We use DECIMAL(10,2) because it is suitable for storing monetary amounts with two decimal places.

JSON objects can contain other objects, so a field may be several levels inside a document.

For example, order 1001 contains:

"shipping": {
  "city": "Bengaluru",
  "priority": true
}

To read the city, we can use the path:

metadata ->> '$.shipping.city'

This tells SQL to find shipping first, then read its city. We can also extract the shipping object before reading the city from it.

SELECT
    order_id,
    metadata ->> '$.shipping.city' AS by_path,
    (metadata -> '$.shipping') ->> '$.city' AS step_by_step
FROM order_events
WHERE order_id IN (1001, 1004)
ORDER BY order_id;

Both expressions return Bengaluru for order 1001. Order 1004 has "shipping": null, so there is no nested city to extract. Both expressions return SQL NULL.

A single path is usually easier to read. Chained extraction is useful when we already have a nested object and want to read more fields from it.

We previously saw that DuckDB allows a top-level key name without $. An analyst tries the same approach for a nested field.

SELECT
    order_id,
    metadata ->> 'payment.amount' AS amount
FROM order_events
WHERE order_id = 1001;

What if the key itself contains a dot?

JSON allows key names containing dots. Consider this document:

{
  "payment.amount": 5,
  "payment": {
    "amount": 1500
  }
}

There are two different values:

• A top-level key named payment.amount, containing 5.
• A nested field payment.amount, containing 1500.

We can distinguish them using the path syntax:

SELECT
    doc ->> 'payment.amount'     AS bare_key,
    doc ->> '$."payment.amount"' AS quoted_key,
    doc ->> '$.payment.amount'   AS path
FROM (
    SELECT CAST(
        '{"payment.amount":5,"payment":{"amount":1500}}'
        AS JSON
    ) AS doc
);

The first two expressions read the top-level key whose name contains a dot. The third expression follows the nested path through payment to amount. Within JSONPath, double quotes can identify a key containing a dot or another character that would otherwise have special meaning.

JSON keys are case-sensitive

Another common mistake is using the wrong letter case. For example, suppose we write Amount instead of amount:

SELECT
    SUM(
        CAST(
            metadata ->> '$.payment.Amount'
            AS DECIMAL(10,2)
        )
    ) AS total
FROM order_events;

None of the documents contains Amount with a capital A. Therefore, every extraction returns SQL NULL, and SUM() has no non-NULL values to add. The result is NULL, not zero.

How can we inspect the available keys?

Instead of guessing field names, we can use json_keys().

SELECT
    order_id,
    json_keys(metadata, '$.payment') AS payment_keys
FROM order_events
WHERE order_id = 1001;

This shows the keys present inside the payment object.

Marketing wants to count orders that used a coupon at checkout.

Some orders contain a coupon field, while others do not. For example,

• order 1001 contains: "coupon": "WELCOME10"
• order 1003 contains: "coupon": null

The other orders do not contain a coupon key. These are different situations, and the distinction matters when querying JSON.

An analyst uses COUNT() on the extracted coupon value:

SELECT
    COUNT(metadata -> '$.coupon') AS used_coupon
FROM order_events;

Comparing missing values and JSON null

Let’s inspect the coupon field using both extraction operators and json_exists().

SELECT
    order_id,
    metadata -> '$.coupon'            AS as_json,
    metadata ->> '$.coupon'           AS as_text,
    json_exists(metadata, '$.coupon') AS has_key
FROM order_events
ORDER BY order_id;

In this table:

• Uppercase NULL represents SQL NULL.
• Lowercase null represents JSON null.

json_exists() checks whether a key or path exists in the document, regardless of the value stored there. That is why it returns true for order 1003, even though the coupon value is JSON null.

Why does the operator matter?

Notice that ->> returns SQL NULL for both an absent key and an explicit JSON null. Therefore:

COUNT(metadata ->> '$.coupon')

returns 1, because only order 1001 provides a non-NULL text value. For counting actual coupon codes, this gives the expected result. However, it does not tell us whether the coupon was explicitly removed or was never recorded.

Understanding the three possible states

In this application’s checkout events, the coupon field has three meanings:

These meanings come from the application’s behaviour. JSON null does not universally mean that something was removed. To report the three states correctly, we need to check both whether the key exists and what type of value it contains.

SELECT
    order_id,
    CASE
        WHEN NOT json_exists(metadata, '$.coupon')
            THEN 'never sent'
        WHEN json_type(metadata, '$.coupon') = 'NULL'
            THEN 'removed'
        ELSE metadata ->> '$.coupon'
    END AS coupon_state
FROM order_events
ORDER BY order_id;

The query first checks whether the key is missing. If the key exists, it then checks whether its JSON type is NULL. Otherwise, it returns the coupon code.

Notice that json_type() returns the text 'NULL' for an explicit JSON null. For a missing path, the function returns SQL NULL. The same distinction applies to other fields. Order 1004 contains "shipping": null, so json_exists(metadata, '$.shipping') returns true.

JSON arrays store lists of values in a specific order. In our sample data, the tags field is an array. For example, order 1001 contains:

"tags": ["new_customer", "promo"]

We can use json_array_length() to count the number of elements in an array.

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

Notice the difference between orders 1004 and 1006:

• Order 1004 has an empty array, so its length is 0.
• Order 1006 has no tags key, so the function returns NULL.

Reading an array element

We can read an individual element using its position inside square brackets.

SELECT
    order_id,
    metadata ->> '$.tags[1]' AS tag
FROM order_events
WHERE order_id = 1001;

JSON arrays vs DuckDB lists

DuckDB also supports SQL lists, but their indexing starts at 1, not 0. This difference becomes important when we extract a JSON array into a DuckDB list. Consider:

SELECT
    order_id,
    metadata ->> '$.tags[0]' AS first_tag,
    metadata ->> '$.tags[#-1]' AS last_tag,
    json_extract_string(
        metadata,
        '$.tags[*]'
    ) AS tag_list,
    json_extract_string(
        metadata,
        '$.tags[*]'
    )[1] AS first_index
FROM order_events
WHERE order_id IN (1001, 1003)
ORDER BY order_id;

There are several things to notice.

[0] inside the JSON path reads the first JSON array element.

[1] applied to the extracted DuckDB list also reads the first element, because DuckDB lists use one-based indexing.

[#-1] is DuckDB’s JSONPath notation for reading the last array element.

[*] selects all elements in the array.

If we request a position that does not exist, such as [5] in these two-element arrays, the JSON extraction returns NULL.

Checking whether an array contains a value

Suppose Marketing wants to find orders that have the tag promo.

After extracting the tags into a DuckDB list, we can use list_contains().

SELECT order_id
FROM order_events
WHERE list_contains(
    json_extract_string(metadata, '$.tags[*]'),
    'promo'
)
ORDER BY order_id;

The query checks whether the list contains an element exactly equal to promo. This is safer than searching the serialised JSON text using:

LIKE '%promo%'

A text search might also match a different tag such as promo_code. An array membership check compares complete elements instead.

Marketing wants a report showing how many orders have each tag and the total order amount associated with that tag.

The problem is that one order can have several tags. For example, order 1001 has both new_customer and promo. To group orders by tag, we first need to turn each array element into a separate row. DuckDB provides unnest() for this purpose.

SELECT
    order_id,
    unnest(
        json_extract_string(metadata, '$.tags[*]')
    ) AS tag
FROM order_events
ORDER BY order_id, tag;

How does UNNEST work?

unnest() expands a list so that each element appears in its own row. For example, order 1001 has two tags, so it produces two rows. Order 1003 also has two tags and produces two rows. Orders with one tag produce one row each.

But orders 1004 and 1006 disappear. Why?

• Order 1004 has an empty tags array.
• Order 1006 has no tags field.

In both cases, the wildcard extraction produces no elements for unnest() to return.

This is correct when we want a list of existing tags, but it is a problem if every order must remain in the result.

Keeping orders without tags

We can use LEFT JOIN LATERAL, which we studied in P10. The lateral subquery expands the tags for each order. The LEFT JOIN keeps the order even when there are no tags to return.

SELECT
    e.order_id,
    t.tag
FROM order_events AS e
LEFT JOIN LATERAL (
    SELECT
        unnest(
            json_extract_string(e.metadata, '$.tags[*]')
        ) AS tag
) AS t ON true
ORDER BY e.order_id, t.tag;

All seven orders now appear. Orders 1004 and 1006 each have one row with a NULL tag. However, the result contains nine rows because orders with multiple tags still appear more than once.

Calculating totals by tag

We can now build a report that groups the expanded rows by tag. First, extract the amount and expand the tags. Then aggregate the result.

WITH tagged AS (
    SELECT
        order_id,
        CAST(
            metadata ->> '$.payment.amount'
            AS DECIMAL(10,2)
        ) AS amount,
        unnest(
            json_extract_string(metadata, '$.tags[*]')
        ) AS tag
    FROM order_events
)
SELECT
    tag,
    COUNT(*) AS orders,
    SUM(amount) AS amount
FROM tagged
GROUP BY tag
ORDER BY tag;

Each row tells us how many orders have that tag and the total amount associated with those orders. For example, the promo tag belongs to orders 1001 and 1003: $1,500 + $3,600 = $5,100

This is correct for the promo group.

Why can’t we add the tag totals together?

The amounts across the tag groups add up to $17,350. However, the five orders that have tags are worth only $12,250 in total. The difference is $5,100. Why?

Orders 1001 and 1003 each have two tags, so their amounts contribute to two different groups.

Their combined amount is:

$1,500 + $3,600 = $5,100

That amount is counted a second time when we add the tag totals together. This is another example of row multiplication, similar to join fan-out from P07.

The tag groups overlap because one order can belong to multiple tags. Therefore, summing the amounts from every tag group does not produce the total order value. If you need the overall order total, calculate it from the original order-level rows.

This example also assumes a tag appears only once within an order. If repeated tags are possible, remove duplicate (order_id, tag) pairs before calculating per-tag counts and amounts.

Finding orders with a tag without expanding rows

If the requirement is simply to find orders containing promo, we do not need to expand the main result.

We can use list_contains(), as in section 07, or an EXISTS subquery.

SELECT order_id
FROM order_events AS e
WHERE EXISTS (
    SELECT 1
    FROM unnest(
        json_extract_string(e.metadata, '$.tags[*]')
    ) AS t(tag)
    WHERE t.tag = 'promo'
)
ORDER BY order_id;

EXISTS checks whether at least one matching tag exists and keeps the outer order row once.

Arrays can also contain objects

So far, our arrays have contained strings. But JSON arrays can also contain objects.

Consider two saved shopping baskets:

{
  "items": [
    {"sku": "MOUSE-01", "qty": 2},
    {"sku": "SLEEVE-03", "qty": 1}
  ]
}

A second basket contains:

{
  "items": [
    {"sku": "MOUSE-01", "qty": 1}
  ]
}

Suppose we want one row per basket item, including its SKU and quantity.

We can use json_extract() to keep each element as JSON, expand the array, and then extract fields from each object.

WITH baskets AS (
    SELECT *
    FROM (
        VALUES
            (
                1,
                CAST(
                    '{"items":[{"sku":"MOUSE-01","qty":2},{"sku":"SLEEVE-03","qty":1}]}'
                    AS JSON
                )
            ),
            (
                2,
                CAST(
                    '{"items":[{"sku":"MOUSE-01","qty":1}]}'
                    AS JSON
                )
            )
    ) AS b(basket_id, doc)
)
SELECT
    basket_id,
    item ->> '$.sku' AS sku,
    CAST(item ->> '$.qty' AS INTEGER) AS qty
FROM baskets,
     unnest(json_extract(doc, '$.items[*]')) AS t(item)
ORDER BY basket_id, sku;

Each result row now represents one item in one basket. We could use this result to calculate total quantities by SKU. A basket with an empty or missing items array would produce no item rows. If we need to preserve those baskets, we can use the same LEFT JOIN LATERAL approach.

We have learned how to extract JSON fields and convert them to SQL types. Now let’s use those fields in filters and reports.

Suppose the courier team wants to find orders whose shipping city is Dubai.

An analyst remembers that -> returns JSON strings with double quotes and writes the comparison using a JSON string.

SELECT order_id
FROM order_events
WHERE metadata -> '$.shipping.city' = '"Dubai"';

Why does the query fail?

Operator precedence determines which parts of an expression SQL evaluates together.

DuckDB’s -> operator has relatively low precedence because the same operator is also used for lambda expressions. In this query, DuckDB effectively interprets the condition as:

metadata -> ('$.shipping.city' = '"Dubai"')

It compares the two text literals first. That comparison produces a Boolean value, which DuckDB then incorrectly tries to use as a JSON extraction path.

The fix: Use parentheses

We can make the intended order explicit by wrapping the JSON extraction in parentheses.

SELECT order_id
FROM order_events
WHERE (metadata -> '$.shipping.city') = '"Dubai"';

Now SQL extracts the JSON value before comparing it. There is another important detail. Because -> returns JSON, the comparison value must also be valid JSON. The SQL literal '"Dubai"' contains a JSON string with double quotes inside the SQL single quotes.

If we write:

SELECT order_id
FROM order_events
WHERE (metadata -> '$.shipping.city') = 'Dubai';

DuckDB raises a malformed-JSON conversion error because Dubai without JSON double quotes is not valid JSON text.

A simpler approach: Compare SQL text

When comparing a JSON string with ordinary text, ->> is easier to use.

SELECT order_id
FROM order_events
WHERE (metadata ->> '$.shipping.city') = 'Dubai';

Now both sides of the comparison are SQL text. There is no need to include JSON quotation marks around Dubai. Parentheses are still a useful habit when an arrow extraction is combined with other operators.

Similar precedence problems can occur with conditions such as LIKE, IS NULL, or expressions that combine extraction with string concatenation.

Building reports from extracted fields

For larger reports, it is useful to extract the JSON fields into named columns before aggregating them.

Suppose Finance wants to know:

• How many orders came from each channel.
• The total recorded order amount for each channel.
• How many orders were marked as priority shipping.

We can create a CTE that extracts and converts the required values.

WITH orders_flat AS (
    SELECT
        order_id,
        metadata ->> '$.channel' AS channel,
        CAST(
            metadata ->> '$.payment.amount'
            AS DECIMAL(10,2)
        ) AS amount,
        CAST(
            metadata ->> '$.shipping.priority'
            AS BOOLEAN
        ) AS priority
    FROM order_events
)
SELECT
    channel,
    COUNT(*) AS orders,
    SUM(amount) AS amount,
    COUNT(*) FILTER (
        WHERE priority
    ) AS priority_orders
FROM orders_flat
GROUP BY channel
ORDER BY channel;

The CTE converts the JSON values into ordinary SQL columns. This makes the outer query easier to read because it no longer needs repeated JSON paths. The Boolean conversion turns text such as true and false into SQL Boolean values. The filtered count includes only rows where priority is TRUE. Rows with missing priority information are not counted as priority orders.

Why is the web amount lower than expected?

The web channel contains four orders, but its total amount includes only three. Order 1006 has no $.payment.amount field. The extraction returns SQL NULL, and SUM() ignores that missing value. As a result, the report counts order 1006 in COUNT(*) but does not include its $3,600 amount.

This is an important problem with JSON reports: a query can count every row while silently missing values from some documents.

We will fix this in the next section.

Order 1006 has been missing from our amount calculations because its JSON document uses an older structure. Most of the newer events store payment details inside a payment object.

But order 1006 contains:

{
  "app_version": 1,
  "channel": "web",
  "amount": 3600,
  "payment_method": "card",
  "ship_to": "London"
}

Here, amount, payment_method, and ship_to are stored directly at the top level. The newer documents store the same information in nested objects.

This is a common problem when applications change over time. Different versions may produce JSON documents with different field names or structures.

Inspecting the available keys

Before deciding how to extract a value, we should understand which fields exist across our documents.

DuckDB’s json_keys() lists the keys inside a JSON object. We can combine it with unnest() to count how often each top-level key appears.

SELECT
    key,
    COUNT(*) AS events
FROM order_events,
     unnest(json_keys(metadata)) AS k(key)
GROUP BY key
ORDER BY events, key;

The result shows that:

• channel appears in all seven documents.
• payment, shipping, and tags appear in six documents.
• amount, app_version, payment_method, and ship_to appear only once.

These less common fields are a useful place to investigate. In our data, they all belong to order 1006, which came from app version 1. The newer events use nested objects, while version 1 stored those values at the top level. The coupon field appears twice: once with a coupon code and once with JSON null.

Extracting amounts from both versions

We now know that the amount can appear in two locations:

• Version 1: $.amount
• Newer structure: $.payment.amount

We can use a CASE expression to choose the correct path based on the application version.

SELECT
    order_id,
    metadata ->> '$.app_version' AS app_version,
    CAST(
        CASE
            WHEN (metadata ->> '$.app_version') = '1'
                THEN metadata ->> '$.amount'
            ELSE metadata ->> '$.payment.amount'
        END AS DECIMAL(10,2)
    ) AS amount
FROM order_events
ORDER BY order_id;

Every order now has an amount. For order 1006, the CASE expression reads the top-level amount. For the other orders, it reads payment.amount. This works because the sample data uses the older structure only when app_version = 1.

Could we use COALESCE instead?

For this dataset, we could also write:

COALESCE(
    metadata ->> '$.payment.amount',
    metadata ->> '$.amount'
)

COALESCE returns the first non-NULL value. If the newer path exists, it uses that value. Otherwise, it tries the older path. Both approaches work for our current data.

The difference is that CASE expresses the version rule, while COALESCE expresses the fallback rule. If documents can contain both fields, these approaches may choose different values depending on the conditions.

Checking whether the known paths cover every document

Supporting two document structures does not guarantee that every future event will use one of them. Suppose a later application version moves the amount to a third location. Both existing paths could return NULL, causing the amount to disappear from reports again.

We can check how many documents contain a non-NULL value at each known path.

SELECT
    COUNT(*) AS events,
    COUNT(
        metadata ->> '$.payment.amount'
    ) AS new_shape,
    COUNT(
        metadata ->> '$.amount'
    ) AS old_shape,
    COUNT(*) FILTER (
        WHERE (metadata ->> '$.payment.amount') IS NULL
          AND (metadata ->> '$.amount') IS NULL
    ) AS neither
FROM order_events;

The result shows that:

• Six documents provide an amount at the newer path.
• One document provides an amount at the older path.
• No document is missing an amount at both paths.

For our sample, all seven documents are covered.

Is this complete validation?

No. This query checks whether a non-NULL value exists at the expected locations. It does not prove that the value is a valid amount. For example, a document containing:

"amount": "unknown"

would pass a non-NULL check but fail when converted to DECIMAL. Likewise, a future document could contain both old and new amount fields with different values.

In that case, we would need to decide which value should take priority and check whether the two values agree.

Handling other changed fields

The older structure also stores:

• Payment method under payment_method.
• Shipping city under ship_to.
• No tags or priority-shipping flag.

These fields need the same careful handling as the amount. For example, an older event that does not record a priority flag should not automatically be treated as false. There is a difference between priority shipping being false and priority shipping information never being recorded.

So far, we have focused on reading JSON. But SQL queries may also need to create JSON documents, for example when preparing data for an API, an export, or another application. DuckDB provides json_object() to build a JSON object from keys and values.

Creating a JSON object

Suppose we want to create a smaller summary for each order containing its ID, channel, and coupon.

SELECT
    order_id,
    json_object(
        'order_id', order_id,
        'channel', metadata ->> '$.channel',
        'coupon', metadata -> '$.coupon'
    ) AS summary
FROM order_events
WHERE order_id IN (1001, 1002)
ORDER BY order_id;

json_object() builds a new JSON object from alternating keys and values. But notice something important about order 1002. Its original document did not contain a coupon key. The new summary contains: "coupon": null

Why did the missing key become JSON null?

The extraction for order 1002 returns SQL NULL because the coupon key is missing. When json_object() receives that SQL NULL as a value, it writes JSON null into the new object. Therefore, the new document no longer preserves the difference between:

• A coupon key that was never present.
• A coupon key explicitly set to JSON null.

Comparing JSON objects

Another challenge appears when we compare two JSON objects. Suppose the payment gateway records this payment:

{
  "amount": 3200,
  "method": "card"
}

We want to find orders whose payment details match this information.

SELECT order_id
FROM order_events
WHERE (metadata -> '$.payment')
    = '{"amount":3200,"method":"card"}';

Why does the comparison fail?

In JSON, object key order does not change the meaning of the object. However, DuckDB’s JSON equality behaviour can depend on how the JSON values are represented and compared.

In this example, the comparison uses the JSON values’ text representations, where the key order matters.

The extracted object is represented as:

{"method":"card", "amount":3200}

The comparison value is: {"amount":3200, "method":"card"}

Because their text representations differ, the comparison returns no rows. If we use the same key order as the stored document:

SELECT order_id
FROM order_events
WHERE (metadata -> '$.payment')
    = '{"method":"card","amount":3200}';

Now the row matches. Whitespace can also affect representation-based comparisons. For this reason, we should not assume that comparing whole JSON objects always checks whether they contain logically equivalent information.

A safer approach: Compare the required fields

If the requirement is to match a payment method and amount, we can compare those fields directly.

SELECT order_id
FROM order_events
WHERE (metadata ->> '$.payment.method') = 'card'
  AND CAST(
      metadata ->> '$.payment.amount'
      AS DECIMAL(10,2)
  ) = 3200;

Now the query compares:

• Payment method as SQL text.
• Payment amount as a numeric value.

This avoids depending on JSON key order or whitespace. It also allows equivalent numeric representations such as 3200, 3200.00, and "3200" to match after conversion.

Making JSON easier to use in reports

Once we understand the different document structures, we can create a query that extracts the important fields into ordinary SQL columns. This is useful because most reporting queries are easier to write when they work with stable field names and types.

The following query handles both the older and newer event structures.

SELECT
    order_id,
    customer_id,
    metadata ->> '$.channel' AS channel,
    CASE
        WHEN (metadata ->> '$.app_version') = '1'
            THEN metadata ->> '$.payment_method'
        ELSE metadata ->> '$.payment.method'
    END AS payment_method,
    CAST(
        CASE
            WHEN (metadata ->> '$.app_version') = '1'
                THEN metadata ->> '$.amount'
            ELSE metadata ->> '$.payment.amount'
        END AS DECIMAL(10,2)
    ) AS amount,
    CASE
        WHEN (metadata ->> '$.app_version') = '1'
            THEN metadata ->> '$.ship_to'
        ELSE metadata ->> '$.shipping.city'
    END AS ship_city
FROM order_events
ORDER BY order_id;

The result now contains regular SQL columns with predictable names and types. The application-version differences are handled inside the query, so reporting queries do not need to know where each version stored its fields.

Using a view

If many reports need the same extracted fields, we can save this query as a view. For example:

CREATE VIEW orders_flat AS
SELECT ...
FROM order_events;

The full SELECT would contain the extraction and conversion logic shown above. Reports can then query orders_flat as if it were a regular table.

This makes the reporting logic easier to maintain. If a future app version changes the JSON structure again, we can update the view rather than editing every report.

JSON queries can fail with errors, but they can also return incorrect results without warning. You might see missing orders, unexpected NULL values, incorrect totals, or repeated amounts. The table below highlights common problems and their likely causes.

Five checks before trusting a JSON report

When working with JSON data, ask these questions:

1. Which document structures are present? Check field names, nesting, and application-version information.
2. What types do the extracted values have? Do not assume the same field always contains the same type.
3. What do missing keys and null values mean? Decide which cases should be counted, excluded, or reported separately.
4. Am I comparing text, JSON, or numbers? Convert values explicitly when required.
5. What does one result row represent? After expanding arrays, check whether original rows disappear or values repeat.

The concepts in this lesson apply to a Postgres database, but the syntax and behaviour of JSON functions are not always the same. A query that works in one database may need changes in another. Even when the syntax looks similar, the extracted value’s type or null handling may differ.

PostgreSQL provides two JSON data types:

• json stores the original JSON text.
• jsonb stores JSON in a processed binary format designed for efficient querying and indexing.

For most workloads involving JSON queries, jsonb is commonly preferred. Here are the PostgreSQL equivalents of the operations we used in DuckDB:

PostgreSQL’s arrow operators use individual keys or array positions. JSON array positions start at 0. Negative indexes can access elements from the end. PostgreSQL also provides JSONPath functions for more complex paths.

When expanding arrays, a LEFT JOIN LATERAL can preserve rows whose arrays are empty or missing.

One important difference concerns JSON equality. PostgreSQL jsonb compares object contents without depending on the original key order or whitespace. The json type does not support the same ordinary equality operator. For numeric conversions, extract JSON strings as text before casting them to SQL numeric types.

What should you check when changing databases?

Before moving a JSON query from one SQL engine to another, verify:

1. Path syntax: Does the engine use JSONPath strings, individual key names, or a different notation?
2. Extraction types: Does the function return JSON, text, or a native SQL value?
3. Null handling: How does it distinguish a missing key, JSON null, and SQL NULL?
4. Array expansion: What happens when an array is empty or missing?
5. Equality rules: Are JSON objects compared by their contents or by their stored representation?

These differences matter because a query may run successfully after migration but produce different results.

JSON allows a SQL column to store nested objects, arrays, and documents whose structure may vary. To query JSON reliably, understand where the required fields are stored, what types their values have, and how missing fields differ from explicit JSON null.

Use numeric and Boolean conversions before calculations, and check how expanding arrays affects row counts and totals. When applications change their JSON structure, handle known versions explicitly and validate that new documents still contain the required information.

Next, in P15, we will learn how window functions calculate rankings and aggregates across related rows while keeping individual rows visible.