JSONB: отличаем отсутствующий ключ от явного null

Сервис получает частичное изменение профиля. Отсутствующий phone означает оставить телефон прежним, а phone: null — удалить его. После извлечения через ->> оба случая выглядят как SQL NULL, и обработчик не понимает намерение клиента.

Текстовое извлечение удобно для чтения значения, но не сохраняет все различия структуры JSON. Сначала проверьте наличие ключа оператором ?, затем извлекайте и применяйте значение. При таком порядке отсутствие поля остаётся отдельным состоянием.

Как изменить решение

WITH payloads(doc) AS (
  VALUES ('{}'::jsonb), ('{"phone":null}'::jsonb),
         ('{"phone":"123"}'::jsonb)
)
SELECT doc ? 'phone' AS supplied,
       jsonb_typeof(doc -> 'phone') AS json_type,
       doc ->> 'phone' AS phone
FROM payloads;

Для пустого объекта supplied будет false. Для явного JSON null supplied будет true, а json_type — строкой null. В обоих случаях phone окажется SQL NULL. Для строки 123 тип будет string, а текстовое значение сохранится.

Обновление должно опираться на два признака: передано ли поле и какое значение передано. Если строите документ из входного словаря до SQL, не удаляйте ключи с null автоматически: это уничтожает команду очистки. Проверка типа нужна отдельно, если число или массив недопустимы для телефона.

Что проверить на своих данных

Набор тестов состоит из пустого объекта, явного null, пустой строки и обычного значения. Сравните состояние профиля после каждого варианта. Пустая строка тоже требует бизнес-решения: хранить её, считать удалением или отклонять. JSON-оператор не выбирает это правило.

Такая модель полезна и для настроек, где значение может быть унаследовано, сброшено или переопределено. Не сводите эти состояния к одному COALESCE: подстановка старого значения при любом NULL мешает удалению. Сначала определите протокол изменения, затем подберите выражение SQL, которое сохраняет различия этого протокола.

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: JSONB — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.