Three kinds of nothing

A lesson from Successful 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 raises an error I would see.

  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 the type they return, not in how they treat a missing key. -> returns jsonb, so a string leaf keeps its JSON quotes. ->> returns text, which is why almost every extraction in production code is a ->>. Through ->>, several different documents print the same blank cell, and the rest of this lesson separates them. The warranty desk's report reads payload ->> 'extended_cover' for every row. Eleven months later, a payout audit traces wrong payouts to that 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 missing value. 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 jsonb_typeof names the JSON null as a type, and IS NULL treats it as a value: '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. The = operator exists only because of the column type: comparison operators are documented as "available for jsonb, though not for json", so against a json column, payload -> 'extended_cover' = 'null'::json has no = operator and is rejected with operator does not exist: json = json. Write = NULL instead and the comparison chapter says not to, "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 comes from a form version without the question. The warranty desk's remediation for the first is a letter and for the second is a backfill, and the error in the report that merged the two cases was blamed on the analyst who produced it.

• COALESCE acts only on an SQL NULL. An empty string is not one.

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

  The claims table has five rows. Claim 1 holds "yes", and the report prints the other four with a blank cover value. Wrap the claims export in a fallback so that no row can leave it without a cover value, coalesce(payload -> 'extended_cover', '"unset"'::jsonb), and decide which of those four claims 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 checks 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 COALESCE returned the first argument. 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 passes 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 export treats the other two as answers. The extended-cover letter is sent for the claim with the empty string, to a customer who never answered the question, and the customer's complaint to the contact centre is logged against the letter template.

  There is one more reason nobody sees the empty string by looking at the output. The client renders an SQL NULL and an empty string identically by default, as blank cells in the same column, so a person reading the output cannot see the difference. Set \pset null to a visible marker, as the figures in this lesson do, and the two print differently.

• A path that fails at any step returns the same NULL

  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 documentation states the return-NULL rule once, for every extraction operator. "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 ->>. A deeper path gets no extra reporting.

  A three-step path can break at the first, second or third step, and each break returns the same SQL NULL. Through #>>, a leaf that holds the JSON null returns that same NULL too.

  Nothing in that sentence is a defect. A path expression is one request, and the answer describes the request. Depth only raises the number of different facts that produce the same result. A renamed intermediate object, a leaf that was never written and a leaf written as JSON null all return the same SQL NULL through #>>. None of the three changes a column, so a schema review shows none of them.

  So diagnose by examining the document, not the extraction result. 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 failed no test.

• An existence test answers the presence question; an extraction does not

  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 in the figure matched exactly one row. The first filter returns three rows for three different facts, and nothing in its result tells them apart. For eleven months the warranty desk reported that number as correct, because nobody had a reason to doubt the query.

  ? 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 matters: it does not find a nested key. It answers independently of the value, so it returns true for a key holding JSON null as it does for "yes". For a nested key, use the jsonpath test instead: payload @? '$.claim.cover.tier' is true when that key exists, even if it holds JSON null.

  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. Using the third one to answer the first question causes the bug, and you cannot see that bug 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 returns NULL when the column is NULL

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

  Count the rows that NOT (payload ? 'extended_cover') should return over the five claims, then read the answer: it returned 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

  A NULL column is a different kind of nothing: the JSON null, the missing key and the empty string are all inside the document, and this one is the column itself. ->, ->>, #>, #>>, jsonb_typeof and ? all return an SQL NULL for an SQL NULL input, so the diagnostic has the same ambiguity it was added to resolve.

  The next lesson explains why the row is skipped with no error. The guard is a separate predicate on the column: before asking what is in the document, ask whether there is one.

  Nobody reports this failure as a query bug. The reconciliation is short by the number of claims whose intake failed outright, which is the group most likely to matter, and when finance eventually asks, the question is why the total changed.