Куда делось поле: ключа нет, null и NULL

Комендант собирает портрет покупателей. Кто эти десять человек, как они пользовались магазином, что настраивали в приложении. Начать предлагает с простого: сколько покупателей выбрали тёмную тему оформления, сколько светлую и сколько не выбрали никакую.

Доставать значения ты уже умеешь: settings ->> 'theme'. Запрос готов за минуту. Тёмная тема у троих, светлая у двоих, а дальше в колонке пестрит NULL. Четыре NULL на десять покупателей. Комендант записывает в сводку: «тему не выбрали четверо».

Ты открываешь документы этих четверых по одному, и сводку приходится отложить. У покупателя 17 ключа theme в настройках нет вообще. У покупателей 31 и 91 ключ есть, а значение у него null. У покупателя 64 нет даже документа: колонка settings пуста.

КВЕРИ: В таблице четыре одинаковых NULL, а причины у них разные. Посчитаем каждую отдельно.

Нам нужно научиться различать эти пустоты запросом. Почему у каждого из четверых пусто по-своему и что это значит для магазина, обсудим в пятой главе. Пока задача техническая: если оставить все четыре строки одним NULL, метрика по необязательному полю легко ответит совсем не на тот вопрос.

Запрос коменданта

Повторим запрос коменданта и внимательно посмотрим на результат. В user_settings десять покупателей, по строке на каждого. В документе настроек ключ theme может хранить dark или light.

Перед запуском прикинь, сколько строк вернётся и у скольких в колонке theme окажется NULL. Заодно найди глазами покупателя 56: его ячейка theme не NULL, но названия темы там тоже нет.

Что лежит за каждым NULL

Двойная стрелка показала четырём покупателям одинаковый результат. Теперь выведем сами документы и посмотрим, действительно ли настройки у них одинаковые.

Перед запуском попробуй найти различия глазами. У покупателя 17 поищи в документе слово theme. У 31 и 91 посмотри, что стоит после двоеточия у theme. У покупателя 64 обрати внимание уже не на содержимое, а на всю ячейку.

Три пустоты

Эти случаи дальше нужно различать, поэтому дадим каждому короткое имя.

Ключа нет. В документе нет записи theme. Так выглядит покупатель 17: коробка с настройками есть, а отделения для темы в ней нет.

JSON null. Ключ есть, значение null. Это покупатели 31 и 91: отделение подписано, но внутри пусто. Такое значение кто-то явно записал в документ, само оно там не возникло.

SQL NULL. Самого документа нет, ячейка settings пуста. Это покупатель 64: посылка не приехала, поэтому открывать нечего.

На схеме три пустоты стоят рядом с обычным значением покупателя 23. Под каждой коробкой показано, что вернут инструменты из этого урока. Полную таблицу пока не заучивай: дальше соберём её по одной колонке.

Что отвечают стрелки

Проверим каждую пустоту стрелками из прошлого урока. Начнём с покупателя 31. Ключ theme у него есть, поэтому -> вернёт содержимое ключа: JSON-значение null. У этого значения тип jsonb, оно действительно лежит в документе. Значит, settings -> 'theme' IS NULL для покупателя 31 даст ложь.

Теперь двойная стрелка. ->> должна вернуть содержимое ключа текстом, но у JSON null текстового значения нет. PostgreSQL выдаёт SQL NULL. Поэтому settings ->> 'theme' IS NULL для покупателя 31 уже истинно.

У 17 и 64 обе стрелки дадут SQL NULL: у первого нет нужного ключа, у второго нет документа, из которого можно что-то достать.

Колонку arrow я снова привожу к text. Так JSON null видно словом null, и в результате он не сливается с настоящим NULL. Заполни в уме строки для покупателей 17, 23, 64 и 91, затем запускай.

IS NULL сливает три случая в один

Комендант выбирал покупателей условием WHERE settings ->> 'theme' IS NULL. PostgreSQL спокойно выполняет запрос и возвращает четверых. Если спросить «у кого в отчёте не будет названия темы», ответ верный. Но вопрос был другим: кто не выбрал тему? Здесь четыре строки уже нельзя складывать в одну группу. Среди них покупатель без ключа, двое с явным null и один вообще без документа.

На count разница видна ещё лучше. Агрегат count(выражение) считает только строки, где выражение не NULL. Поэтому count(settings -> 'theme') и count(settings ->> 'theme') на этой таблице дадут разные числа. Первый не посчитает 17 и 64, второй дополнительно пропустит 31 и 91.

До запуска попробуй назвать все четыре числа. Для первых двух уже есть почти весь расчёт: строк в таблице десять, а документа нет только у покупателя 64.

Есть ли ключ: оператор ?

Для первой пустоты есть отдельная проверка. Оператор ? отвечает, есть ли на верхнем уровне документа нужный ключ. Запись settings ? 'theme' смотрит только на наличие theme; содержимое ключа её не интересует. Поэтому для покупателя 31 результат true, хотя внутри лежит null.

У покупателя 17 ключа нет, поэтому получаем false. С покупателем 64 хитрее: документа нет, проверять нечего, и ? возвращает NULL. Здесь часто ошибаются с отрицанием. Условие WHERE NOT settings ? 'theme' найдёт 17, но потеряет 64: NOT NULL в SQL остаётся NULL, а WHERE пропускает только строки с true.

Есть ещё граница, о которую легко споткнуться: ? смотрит только на верхний уровень. У многих покупателей внутри notifications есть ключ email, однако settings ? 'email' даст false. До вложенного notifications.email этот оператор не добирается.

Перед запуском предскажи три колонки: has_theme, has_experiments и has_email_top. С experiments поможет содержимое документов: этот ключ записан только участникам экспериментов приложения. Отдельно подумай, что получится у покупателя 64 сразу во всех трёх колонках.

JSON null: jsonb_typeof(…) = 'null'

Для второй пустоты пригодится функция из первого урока. jsonb_typeof(settings -> 'theme') сообщает тип значения под ключом. У тем dark и light тип string. У покупателей 31 и 91 функция вернёт слово null: значение в документе есть, и его JSON-тип называется именно так.

У 17 и 64 сначала сама стрелка вернула SQL NULL. Типа у отсутствующего значения нет, поэтому jsonb_typeof тоже даст NULL. Значит, условие jsonb_typeof(settings -> 'theme') = 'null' выберет ровно 31 и 91: ключ у них есть, а внутри записан JSON null.

В условии 'null' — обычный текст с названием типа. Функция так же возвращает слова string, number, null и другие названия. Здесь мы сравниваем название типа, а не JSON-значение.

Есть и более короткая запись: settings -> 'theme' = 'null'. Литерал справа станет JSON-значением null, после чего PostgreSQL сравнит два значения jsonb. Результат тот же. Я предпочитаю jsonb_typeof: из запроса сразу видно, что нас интересует именно тип.

С двойной стрелкой этот приём уже не работает. Условие settings ->> 'theme' = 'null' не найдёт ни одной строки: ->> успела превратить JSON null в SQL NULL, а сравнение NULL с чем угодно даёт NULL. Текст null появился бы только из JSON-строки "null", то есть четырёх букв внутри кавычек. В наших настройках такой строки нет.

Перед запуском угадай ещё одну строку результата: какой тип получит покупатель 56 с пустой строкой вместо темы?

Сводная таблица на литералах

На покупателях мы проверяли инструменты по очереди. Теперь уберём всё лишнее и поставим четыре случая рядом на литералах: документ с темой, документ с "theme": null, пустой документ {} и SQL NULL вместо документа.

Кроме самого литерала здесь пять колонок. Сначала заполни всю таблицу в уме, потом запускай. Я бы особенно внимательно посмотрел на arrow_is_null во второй строке и has_key в последней: именно в этих двух клетках чаще всего ошибаются.

Все случаи в одной колонке

Подпишем каждый вид пустоты прямо в запросе. CASE проверяет условия сверху вниз и останавливается на первой истинной ветке, поэтому переставлять их здесь нельзя без последствий.

Первой должна идти проверка settings IS NULL. Если поставить её ниже, покупатель 64 дойдёт до NOT settings ? 'theme' и получит NULL. Это не истина, поэтому проверка продолжится. jsonb_typeof(…) = 'null' тоже вернёт NULL, и строка уйдёт в ELSE с подписью значение. Получится явная ошибка в смысле результата: документа в ячейке нет, а запрос назовёт его значением.

Рабочий порядок такой: нет документа, нет ключа, JSON null, значение. После каждой проверки остаются только строки, которые не подошли под предыдущие условия.

Перед запуском реши, в какую группу попадёт покупатель 56.

Теперь повтори тот же приём для другого ключа. digest в этих настройках хранит режим рассылки: weekly, daily или none. Пока ячейка показывает только сырые значения settings ->> 'digest'. Перенеси на digest классификацию из прошлой ячейки и сразу посчитай покупателей в каждой группе. Перед запуском прикинь числа: у покупателя 85 записан null, у 103 пустая строка, а у 17 и 64 ситуация знакомая по теме: у 17 нет ключа, у 64 нет документа.

То же в ответах шлюза

Такая история встречается и за пределами настроек. С 17 февраля платёжный шлюз отвечает по новой версии API, 2025-02: ключ error теперь есть в каждом ответе. У всех ответов без отказа, успешных и в обработке, внутри null, при отказе — объект с кодом ошибки. В старом API, 2024-06, error появлялся только при отказе. В снимке семь старых ответов, с первого по седьмой, и пять новых, с восьмого по двенадцатый.

Допустим, сводку «отказы при оплате» считали условием response ? 'error'. До 17 февраля оно действительно находило отказы: только в таких ответах был этот ключ. После обновления шлюза запрос не упал и продолжил возвращать строки, но теперь вместе с отказами туда попали успешные платежи и платёж в обработке. Метрика выросла, хотя PostgreSQL не сообщил ни об одной ошибке.

Ниже лежат все двенадцать платежей. До запуска назови два числа: сколько строк получат true в has_error_key и у скольких error_kind окажется object.

Снова откроем сводку коменданта. Теперь в неё должны попасть только покупатели, у которых ключ темы записан, а значение сброшено в null. Покупателей без ключа и без документа включать не надо. Запрос ниже выполняется и сейчас возвращает четырёх покупателей: 17, 31, 64 и 91. Двое здесь лишние.

Частые ошибки

Считать ->> IS NULL признаком «ключа нет»

Строка: покупатель 31, "theme": null.

Ожидание: WHERE settings ->> 'theme' IS NULL найдёт покупателей, у которых в настройках нет темы.

На деле запрос вернёт четверых: 17 без ключа, 31 и 91 с null, 64 без документа. После двойной стрелки JSON null становится SQL NULL, поэтому эти случаи уже не различить.

Как проверить: отсутствие ключа ищи через NOT settings ? 'theme', а JSON null через jsonb_typeof(…) = 'null'.

Ждать от -> IS NULL того же, что от ->> IS NULL

Строка: покупатель 91.

Ожидание: settings -> 'theme' IS NULL истинно, ведь вместо темы записано null.

На деле условие ложно. Одинарная стрелка вернула JSON-значение null, у которого есть тип и которое PostgreSQL не считает SQL NULL. Точно так же ведёт себя error в новых ответах шлюза.

Как проверить: выведи рядом (settings -> 'theme')::text. Слово null означает, что стрелка действительно вернула JSON-значение; настоящий SQL NULL так не отображается.

Потерять строки без документа

Строка: покупатель 64, settings равен NULL.

Ожидание: WHERE NOT settings ? 'theme' вернёт всех без ключа темы, включая строки, где нет самого документа.

На деле покупатель 64 исчезнет из результата. ? над NULL возвращает NULL, после NOT результат остаётся NULL, а WHERE такую строку отбрасывает.

Как проверить: сначала вычисли count(*) - count(settings). Если получилось больше нуля, отдельно реши судьбу строк без документа. Если их нужно считать вместе с отсутствующим ключом, напиши это прямо: settings IS NULL OR NOT settings ? 'theme'.

Искать отказы по наличию ключа

Строка: платёж 8, "error": null.

Ожидание: response ? 'error' выбирает неудачные платежи.

На деле условие возвращает шесть платежей из двенадцати, хотя статус failed только у двух. После перехода на новый API ключ error присутствует и в успешных ответах.

Как проверить: поставь рядом count(*) FILTER (WHERE response ? 'error') и count(*) FILTER (WHERE jsonb_typeof(response -> 'error') = 'object'). Здесь получатся разные числа, потому что наличие ключа больше не означает наличие ошибки.

Считать пустую строку пустотой

Строка: покупатель 56, "theme": "".

Ожидание: ->> IS NULL найдёт и эту строку.

На деле '' хранит строку нулевой длины. Для IS NULL это обычное значение, а jsonb_typeof возвращает string.

Как проверить: если в твоей метрике пустая строка тоже означает «ничего», добавь отдельную проверку settings ->> 'theme' = ''. Как трактовать такие значения в отчётах, обсудим в пятой главе.

Перенос: четыре числа перед любой метрикой

Когда мне нужно построить метрику по необязательному полю JSON, я сначала считаю четыре группы: строки без документа, без ключа, с JSON null и с обычным значением. Запрос тебе уже знаком: тот же CASE и GROUP BY, который ты собирал для digest. Вместо одного общего NULL сразу видно, сколько строк пришло в каждом состоянии.

Дальше уже можно решать, какие из этих групп отвечают на вопрос заказчика. Например, «кто не выбирал тему» и «у кого тема сброшена» — разные условия. Если нужны две группы, я прямо перечисляю их через OR, чтобы потом по SQL было понятно, что именно считали.

После смены версии API или приложения такие четыре числа стоит посчитать заново. Вспомни error: формат изменился, часть успешных ответов переехала из «ключа нет» в «JSON null», а старый запрос продолжил работать без единой ошибки.

Однажды я так поймал проблему с push-уведомлениями. Долю покупателей с настроенными уведомлениями считали по наличию ключа. Новая версия приложения стала записывать ключ всем, а тем, кто ничего не настраивал, ставила null. За одну ночь метрика выросла почти до ста процентов. Сам запрос оставался зелёным, поэтому целую неделю этот скачок принимали за успешный релиз.