Three kinds of nothing

A lesson from Green Pipelines, Wrong Numbers. Play it above, or read it through below.

• A read against a key that is not there returns NULL. Nothing fails.

  The common read: The extraction operators validate the request against the document, so asking for a key the document does not have fails loudly enough that I would know.

  Name the error Postgres raises when you ask a document for a key it has never held, and then read the next sentence. There is none. The extraction operators are documented to return NULL rather than failing when the input "does not have the right structure to match the request; for example if no such key or array element exists".

  Read a key the document never had and Postgres

  The two extraction operators differ in what they hand you, not in how forgiving they are. -> returns jsonb, so a string leaf keeps its JSON quotes. ->> returns text, ready to compare against a text column, which is why almost every extraction written in anger is a ->>. That cast is where four situations stop being four. The warranty desk's report reads payload ->> 'extended_cover' for every row, and the audit of who was owed a payout lands eleven months later, filed against the report.

• JSON null is a value the column is holding

  The common read: A key whose value is null is an empty key, so it is the same as a key that is not there and IS NULL will find both.

  The JSON literal null is a stored value, not a hole where one should be. Postgres says so twice in the same documentation entry: jsonb_typeof lists null among the six types it can return, and the entry adds in parentheses that "the null result should not be confused with an SQL NULL". So the type function distinguishes them and IS NULL does not: 'null'::jsonb IS NULL is false, because there is a datum there and it is the JSON null.

  That distinction is testable with an ordinary equality. payload -> 'extended_cover' = 'null'::jsonb compares two real jsonb values and returns true, on a type fact rather than a NULL fact: comparison operators are documented as "available for jsonb, though not for json", so the same expression against a json column has no = operator and is rejected with operator does not exist: json = json. Write = NULL instead and the comparison chapter's rule applies: do not, "because NULL is not 'equal to' NULL".

  A claim recorded as explicitly holding no extended cover is a customer who answered the question. A claim whose intake form never asked is a form version. The warranty desk's remediation for the first is a letter and for the second is a backfill, and the report that merged them was filed against the analyst who produced it.

• COALESCE fires on absence. An empty string is not absent.

  The common read: A blank value and a missing value are the same problem, so one fallback on the extraction handles both.

  Wrap the claims export in a fallback so that no row can ship without a cover value, coalesce(payload -> 'extended_cover', '"unset"'::jsonb), and decide which of the four claims that showed nothing in the report now come out carrying "unset". Hold that set while you read the next line.

  Run that export over the five claims and the fallback fires for

  COALESCE reads one property, whether its argument is an SQL NULL, and nothing about the document underneath. The JSON null and the empty string are values, so on those two claims the first argument won. The empty string is the fourth kind of nothing, the one the title left out because nobody counts it. jsonb_typeof('""'::jsonb) returns string, not null, and ->> on it returns a zero-length text value, not an SQL NULL, so it survives every null guard and fails every check that expects a value.

  The four cases separate by what each one IS rather than by what it looks like: claim 2 has no key, claim 3 a key holding the JSON literal null, claim 4 a key holding a zero-length string, claim 5 no payload. Only two are absences, and the other two reach the customer. The extended-cover letter goes out to the claim with the empty string, addressed to someone who never answered the question, and the complaint comes back through the contact centre against the letter template.

  There is one more reason nobody catches this by looking. The client renders an SQL NULL and an empty string identically by default, as blank cells in the same column, so the first place a human could notice the difference is the last place that shows it. A tool that has an opinion about how to display nothing is a tool that has taken a side in this lesson.

• One NULL covers a path that broke anywhere along it

  The common read: A path is a sequence of lookups, so a path that fails reports which lookup failed, or at least that one did.

  The forgiveness clause is written once, generally. "The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request", and path is in that list on purpose: #> and #>> get the same treatment as -> and ->>. Depth buys no extra reporting, so the collapse widens as documents get deeper: a path of length three has three places to break, one answer for all of them, plus the two a leaf produces alone.

  Nothing in that sentence is a defect. A path expression is one request and the answer is about the request. What changes with depth is how many facts arrive wearing the same face. A renamed intermediate object, a leaf never written, a leaf written as JSON null, and a document whose shape changed all report identically through #>>, and only the last ever gets guessed, because only it shows up in a schema review.

  So the diagnosis has to move off the extraction and onto the document. Read the type at the depth you care about, or ask about the key directly, or walk the path one step at a time and see which step stops returning an object. The claims report's tier column went empty across every row on a Tuesday and the ticket said the report was broken; the intake service had renamed cover to coverage in a release that changed no column and broke no test.

• Only the existence operator answers the presence question

  The common read: I can tell whether a key is in the document by looking at what came out of it, so an extraction plus IS NULL is a presence test.

  Pick the filter you would write to count claims with no extended-cover answer recorded, and hold it while you read the next line.

  payload ->> 'extended_cover' IS NULL over the five claims returns

  Every other filter on the figure matched exactly one row, and the distance between three and one is the entire lesson: one predicate, three facts, no way to tell them apart after the fact. The warranty desk filed that number as clean for a year, credited to a query nobody ever had reason to doubt.

  ? is the operator that asks about the key. It is documented as answering "does the text string exist as a top-level key or array element within the JSON value", and the word top-level is load-bearing: it is not a search, it does not descend, and a key nested one level down is not found by it. What it does do is answer independently of the value, so it returns true for a key holding JSON null exactly as readily as for a key holding "yes".

  Between them, three operators answer the three questions the reader now knows are different: ? says whether the key is there, jsonb_typeof says what kind of value is under it, and ->> says what that value looks like as text. Reaching for the third one to answer the first is the entire bug, and it is a bug you cannot see in a query plan, a test suite or a code review, because the query is correct SQL that returns the wrong three rows.

• The presence test goes NULL when the column does

  The common read: ? answers about the key, so NOT (payload ? 'k') finds every row where that key is missing.

  Count the rows that filter should return over the five claims, then read the answer it actually returned, which is one. Claim 2 has no extended_cover key and matched. Claim 5, whose payload column holds no document at all, did not, because the operator returned neither true nor false for it: with an SQL NULL on the left there is no document to look in, so the test has no answer to give and the row is not returned by a WHERE clause.

  Add NOT (payload ? 'extended_cover') to find every claim missing that key and you

  That is the fourth kind of nothing, one layer up: three of the four live inside the document, and the fourth is the column itself. ->, ->>, #>, #>>, jsonb_typeof and ? all hand back an SQL NULL when handed one, so the diagnostic inherits the ambiguity it was brought in to resolve. Why the skipped row vanishes rather than complaining is the next lesson. The guard is a separate predicate on the column: before asking what is in the document, ask whether there is one.

  Nobody files that one against the query. The reconciliation is short by the number of claims whose intake failed outright, which is the population most likely to matter, and the finance question that eventually arrives asks why the total moved.