A key present-with-null and a key absent are different facts — and your extraction function decides whether you can still tell them apart.

Semi-structured data carries a third state flat columns don't have: said-nothing vs said-null. A device that reported 'tip: null' told you the diner declined; a device that omitted the key may be an older kiosk that never asks — merging them invents a fact. Typed extraction (extract_string, ::INT casts) is where the merge happens: keep the raw JSON operator in the pipeline until after you've branched, then cast.

See it break

orders

oid	payload
1	{"tip":5}
2	{"tip":null}
3	{}

The trap

SELECT
  COUNT(*) AS declined
FROM
  orders
WHERE
  json_extract_string(payload, '$.tip') IS NULL;

The fix

SELECT
  COUNT(*) AS declined
FROM
  orders
WHERE
  json_type(json_extract(payload, '$.tip')) = 'NULL';

The trap

The fix

The declined-tip count read 2, not 1 — json_extract_string collapsed the kiosk that reported tip:null and the kiosk that never asked into one NULL, so a 'said no' and a 'wasn't asked' became the same fact.

The rule

Use the raw-JSON accessor (-> / json_type) to test the three states; cast late, after the distinction is made.

Operator spellings vary (-> / ->>, GET_PATH, JSON_VALUE); the three-state concept is portable — knowing your engine's collapsing function is the fluency.

Shows up elsewhere

NULL = NULL is not TRUE UNNEST re-introduces fan-out

Try one →Prove it in practice →