Where did the field go: missing key, null, and NULL

The Commandant is building a profile of the customers: who these ten people are, how they used the store, what they set up in the app. He suggests starting with something simple: how many customers chose the dark theme, how many the light one, and how many chose neither.

You already know how to pull values out: settings ->> 'theme'. The query is ready in a minute. Dark theme for three, light for two, and then the column is peppered with NULL. Four NULLs out of ten customers. The Commandant writes in the summary: “four customers didn’t choose a theme.”

You open the documents for those four one by one, and the summary has to wait. Customer 17 has no theme key in the settings at all. Customers 31 and 91 have the key, but its value is null. Customer 64 doesn’t even have a document: the settings column is empty.

QUERY: The table shows four identical NULLs, but the reasons behind them differ. Let’s count each reason separately.

We need to learn to tell these kinds of nothing apart with a query. Why each of the four is empty in a different way, and what that means for the store, we’ll discuss in chapter 5. For now the task is technical: if you leave all four rows as one NULL, a metric on an optional field can easily answer a completely different question.

The Commandant’s query

Let’s repeat the Commandant’s query and look closely at the result. user_settings has ten customers, one row per customer. In a settings document, the theme key can hold dark or light.

Before you run it, estimate how many rows will come back and for how many of them the theme column will be NULL. And while you’re at it, find customer 56 by eye: their theme cell isn’t NULL, but there’s no theme name in it either.

What’s behind each NULL

The double arrow gave four customers the same result. Now let’s display the documents themselves and see whether their settings really are the same.

Before you run it, try to spot the differences by eye. For customer 17, look for the word theme in the document. For 31 and 91, look at what comes after the colon for theme. For customer 64, pay attention not to the contents but to the cell as a whole.

Three kinds of nothing

We’ll need to tell these cases apart from here on, so let’s give each one a short name.

No key. The document has no theme entry. That’s customer 17: the box with the settings is there, but it has no compartment for the theme.

JSON null. The key is there, and the value is null. These are customers 31 and 91: the compartment is labeled but empty inside. That value was explicitly written into the document; it didn’t appear there on its own.

SQL NULL. There’s no document at all; the settings cell is empty. That’s customer 64: the parcel never arrived, so there’s nothing to open.

The diagram puts the three kinds of nothing next to customer 23’s ordinary value. Under each box, it shows what the tools from this lesson return. Don’t memorize the full table yet: we’ll build it up one column at a time.

What the arrows return

Let’s test each kind of nothing with the arrows from the last lesson. Start with customer 31. The theme key is there, so -> returns the content of the key: the JSON value null. That value has the type jsonb, and it really is sitting in the document. So settings -> 'theme' IS NULL comes out false for customer 31.

Now the double arrow. ->> is supposed to return the content of the key as text, but a JSON null has no text value. PostgreSQL produces SQL NULL. So settings ->> 'theme' IS NULL is true for customer 31.

For customers 17 and 64, both arrows give SQL NULL: the first has no such key, the second has no document to pull anything from.

I’m casting the arrow column to text again. That way a JSON null shows up as the word null, and in the result it doesn’t blend in with a genuine SQL NULL. Fill in the rows for customers 17, 23, 64, and 91 in your head, then run it.

IS NULL merges three cases into one

The Commandant picked customers with the condition WHERE settings ->> 'theme' IS NULL. PostgreSQL runs the query without complaint and returns four customers. If the question is “who will have no theme name in the report?”, the answer is right. But the question was a different one: who didn’t pick a theme? Here the four rows can no longer be put into a single group. Among them are a customer with no key, two with an explicit null, and one with no document at all.

The difference is even clearer with count. The aggregate count(expression) counts only the rows where the expression isn’t NULL. So count(settings -> 'theme') and count(settings ->> 'theme') will give different numbers on this table. The first won’t count 17 and 64; the second will also skip 31 and 91.

Before you run it, try to name all four numbers. For the first two, almost all the work is already done: the table has ten rows, and only customer 64 has no document.

Does the key exist: the ? operator

For the first kind of nothing, there’s a dedicated check. The ? operator answers whether the document has the key you ask about at the top level. The expression settings ? 'theme' looks only at whether theme is present; it doesn’t care what value the key holds. That’s why the result is true for customer 31, even though its value is null.

Customer 17 has no key, so we get false. Customer 64 is trickier: there’s no document, nothing to check, and ? returns NULL. This is where people often go wrong with negation. The condition WHERE NOT settings ? 'theme' will find 17 but lose 64: in SQL, NOT NULL stays NULL, and WHERE lets through only rows that are true.

There’s another limit that’s easy to trip over: ? looks only at the top level. Many customers have an email key inside notifications, yet settings ? 'email' gives false. The operator doesn’t reach down to the nested notifications.email.

Before you run it, predict three columns: has_theme, has_experiments, and has_email_top. For experiments, the contents of the documents will help: this key is written only for participants in the app’s experiments. And think separately about what customer 64 gets in all three columns at once.

JSON null: jsonb_typeof(…) = 'null'

For the second kind of nothing, the function from the first lesson comes in handy. jsonb_typeof(settings -> 'theme') reports the type of the value under the key. For the themes dark and light, the type is string. For customers 31 and 91, the function returns the word null: there is a value in the document, and its JSON type is called exactly that.

For 17 and 64, the arrow itself returned SQL NULL first. A missing value has no type, so jsonb_typeof gives NULL as well. That means the condition jsonb_typeof(settings -> 'theme') = 'null' will select exactly 31 and 91: they have the key, and it holds a JSON null.

In the condition, 'null' is ordinary text with the name of a type. The function likewise returns the words string, number, null, and the other type names. Here we’re comparing the name of a type, not a JSON value.

There’s a shorter way to write it: settings -> 'theme' = 'null'. The literal on the right becomes the JSON value null, after which PostgreSQL compares two jsonb values. The result is the same. I prefer jsonb_typeof: it shows right in the query that what we care about is the type.

This trick no longer works with the double arrow. The condition settings ->> 'theme' = 'null' won’t find a single row: ->> has already turned the JSON null into SQL NULL, and comparing NULL with anything gives NULL. The text null would appear only from the JSON string "null", that is, four letters inside quotes. There’s no such string in our settings.

Before you run it, guess one more row of the result: what type will customer 56, with an empty string instead of a theme, get?

A summary table using literals

With the customers, we tested the tools one at a time. Now let’s strip out everything extra and set the four cases side by side using literals: a document with a theme, a document with "theme": null, an empty document {}, and a SQL NULL instead of a document.

Besides the literal itself, there are five columns here. Fill in the whole table in your head first, then run it. I’d look especially closely at arrow_is_null in the second row and has_key in the last: those two cells are where people slip most often.

All the cases in one column

Let’s label each kind of emptiness right in the query. CASE checks conditions from top to bottom and stops at the first true branch, so changing their order here has consequences.

The settings IS NULL check has to come first. If you move it lower, customer 64 reaches NOT settings ? 'theme' and gets NULL. That isn’t true, so the checks continue. jsonb_typeof(…) = 'null' also returns NULL, and the row falls into ELSE with the label значение ("value"). That would make the meaning of the result plainly wrong: there is no document in the cell, but the query would label it as a value.

The working order is: no document, no key, JSON null, value. After each check, only the rows that didn’t match the previous conditions remain.

Before you run it, decide which group customer 56 will fall into.

Now repeat the same technique for another key. In these settings, digest stores the newsletter mode: weekly, daily, or none. For now, the cell shows only the raw settings ->> 'digest' values. Apply the classification from the previous cell to digest and immediately count the customers in each group. Before you run it, estimate the counts: customer 85 has null, 103 has an empty string, and 17 and 64 are cases you already know from the theme example: 17 has no key, and 64 has no document.

The same thing in gateway replies

The same problem shows up outside settings too. Since February 17, the payment gateway has been replying using the new API version, 2025-02: the error key is now present in every reply. In every reply without a decline, whether successful or still processing, its value is null; for a decline, it contains an object with the error code. In the old API, 2024-06, error appeared only on a decline. The snapshot contains seven old replies, 1 through 7, and five new ones, 8 through 12.

Suppose the “payment declines” report was calculated with the condition response ? 'error'. Before February 17, it really did find declines: only those replies had that key. After the gateway update, the query didn’t fail and kept returning rows, but now successful payments and the payment still processing were included along with the declines. The metric went up even though PostgreSQL reported no error at all.

Below are all twelve payments. Before you run it, name two numbers: how many rows will get true in has_error_key, and how many will have object in error_kind.

Let’s open the Commandant’s summary again. This time, it should include only customers whose theme key is present but whose value has been reset to null. Customers with no key or no document should not be included. The query below runs and currently returns four customers: 17, 31, 64, and 91. Two of them don’t belong there.

Common mistakes

Taking ->> IS NULL to mean “no key”

The row: customer 31, "theme": null.

You expect: WHERE settings ->> 'theme' IS NULL will find the customers who have no theme in their settings.

In reality, the query returns four: 17 with no key, 31 and 91 with null, and 64 with no document. After the double arrow, a JSON null becomes SQL NULL, so these cases can no longer be told apart.

How to check: look for a missing key with NOT settings ? 'theme', and for a JSON null with jsonb_typeof(…) = 'null'.

Expecting -> IS NULL to behave like ->> IS NULL

The row: customer 91.

You expect: settings -> 'theme' IS NULL is true, because null is written where the theme should be.

In reality, the condition is false. The single arrow returned the JSON value null, which has a type and which PostgreSQL doesn’t consider a SQL NULL. The error key in the gateway’s new replies behaves exactly the same way.

How to check: print (settings -> 'theme')::text next to it. The word null means the arrow really did return a JSON value; a genuine SQL NULL isn’t displayed that way.

Losing the rows with no document

The row: customer 64, settings is NULL.

You expect: WHERE NOT settings ? 'theme' will return everyone without a theme key, including the rows where there’s no document at all.

In reality, customer 64 drops out of the result. ? on NULL returns NULL, after NOT the result is still NULL, and WHERE discards such a row.

How to check: first compute count(*) - count(settings). If it comes out above zero, decide separately what to do with the rows that have no document. If they should be counted together with the missing key, say so outright: settings IS NULL OR NOT settings ? 'theme'.

Finding declines by the presence of a key

The row: payment 8, "error": null.

You expect: response ? 'error' selects the failed payments.

In reality, the condition returns six payments out of twelve, although only two have the status failed. After the move to the new API, the error key is present in successful replies too.

How to check: put count(*) FILTER (WHERE response ? 'error') and count(*) FILTER (WHERE jsonb_typeof(response -> 'error') = 'object') side by side. The numbers will differ, because the presence of a key no longer means the presence of an error.

Counting an empty string as nothing

The row: customer 56, "theme": "".

You expect: ->> IS NULL will find this row too.

In reality, '' is a zero-length string. For IS NULL, that’s an ordinary value, and jsonb_typeof returns string.

How to check: if, in your metric, an empty string also means “nothing,” add a separate check, settings ->> 'theme' = ''. How to treat such values in reports, we’ll discuss in chapter 5.

Applying it: four numbers before any metric

When I need to build a metric on an optional JSON field, I first count four groups: rows with no document, no key, a JSON null, and an ordinary value. You already know the query: it’s the same CASE and GROUP BY you built for digest. Instead of one shared NULL, you can immediately see how many rows arrived in each state.

Then you can decide which of those groups actually answer the stakeholder’s question. For example, “who never chose a theme” and “whose theme was reset” are different conditions. If I need two groups, I list them explicitly with OR, so it’s clear from the SQL what exactly was counted.

After an API or app version change, it’s worth counting those four numbers again. Remember error: the format changed, some successful responses moved from “no key” to “JSON null,” while the old query kept running without a single error.

I once caught a push-notification problem this way. The share of customers with notifications configured was measured by whether the key existed. A new version of the app started writing the key for everyone and putting null for people who hadn’t configured anything. Overnight, the metric jumped to almost a hundred percent. The query itself stayed green, so for an entire week the spike was mistaken for a successful release.