Missing Keys and NULL Extraction

Concepts covered: Null Handling, JSON Extraction, Conditional Logic

"How many orders used no coupon?" The quick query, WHERE JSON_EXTRACT_SCALAR(payload, '$.coupon') IS NULL, returns 3 of the 4 orders, and it is answering a different question. Order 1002 sent "coupon": null: checkout ran the coupon step and the customer applied nothing. Orders 1003 and 1004 never sent the key, because the app builds that wrote them predate the coupon feature. Scalar extraction turns both into the same SQL NULL, so "no coupon" and "we have no idea" land in one bucket.

Key missing

• The writer never produced the field
• Usual cause: an older client or a skipped optional section
• For "used no coupon?" the answer is unknown

Key sent as null

• The writer produced the field with no value
• Usual cause: the user cleared it, or the step ran and found nothing
• For "used no coupon?" the answer is yes

The JSON-returning operator keeps the distinction and the text operator destroys it. Postgres's -> gives back a jsonb null for the explicit null and SQL NULL for the absent key, while ->> maps both to SQL NULL. BigQuery's JSON_VALUE, Trino's JSON_EXTRACT_SCALAR and Snowflake's ::STRING cast all behave like ->>. Once the value is text, the information is gone, so the check runs on the JSON value or on the key itself, never on the extracted string.

Classify into 3 states first

-- Postgres: present, explicit null, or never sent
SELECT
  CASE
    WHEN NOT (payload ? 'coupon')                 THEN 'not_sent'
    WHEN jsonb_typeof(payload->'coupon') = 'null' THEN 'sent_null'
    ELSE 'applied'
  END      AS coupon_state,
  COUNT(*) AS orders
FROM orders_raw
GROUP BY 1
ORDER BY 1;

The answer to "how many used no coupon" is 1 confirmed, with 2 unknown, and saying that split out loud is the moment the interviewer is waiting for. Reporting 3 makes a confident claim about orders the data says nothing about, and it is the version that ends up on a dashboard comparing coupon uptake across app releases, where older releases look like customers who turned coupons down.

The same split applies to array elements. If a client posts "items": [{"sku": "A-12", "qty": 1}, null], the flatten returns 2 element rows and the second holds a JSON null. In Postgres COUNT(e) counts it, since a jsonb null is a value, while COUNT(e->>'sku') does not, so line counts disagree depending on which column is counted. Decide whether a null element is a line, then filter with jsonb_typeof(e) <> 'null' where it is not.

The probe in other engines

Snowflake turns the trap around. A VARIANT holding JSON null is a value, so payload:coupon IS NULL is false for order 1002, and a filter written to catch every no-coupon order catches only the missing keys. The BigQuery row assumes payload uses the native JSON type. Loading can also erase the difference before any query runs: STRIP_NULL_VALUES = TRUE on a Snowflake JSON file format removes every key whose value is null, turning sent_null orders into not_sent orders permanently.

Where the difference changes the outcome

Partial-update feeds are the follow-up that tests production experience. Under JSON Merge Patch (RFC 7396), {"coupon": null} means remove the coupon and an absent coupon means leave it as it was. A change-data consumer that extracts with ->> and writes the result into a target column applies a NULL in both cases, wiping fields the source never touched. The same split decides feature flags (switched off versus never configured) and survey answers (declined versus never shown).

COALESCE(payload->>'coupon', 'none') is the line that looks tidy in a dashboard and merges 2 meanings under one label. Classify into the 3 states first, then pick a default inside each state, and keep not_sent visible as its own value so the unknowns stay counted rather than relabelled.

Do

• Classify present, JSON null and missing before any COALESCE or IS NULL
• Test for the key on the JSON value: ?, JSON_QUERY or the VARIANT itself
• Report not_sent as its own bucket so unknowns stay counted
• Check the loader for STRIP_NULL_VALUES before trusting a null count

Avoid

• Don't extract to text and then ask whether the key existed
• Don't COALESCE a missing key and a JSON null into one default label
• Don't write ->> output into a target column from a merge-patch feed
• Don't assume IS NULL catches a JSON null held in a Snowflake VARIANT

About this lesson

This section is part of the Semi-Structured Data: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.