JSONB_TYPEOF in PostgreSQL: Guarding Dynamic JSON Safely

JSONB подкупает гибкостью: складывай в колонку что угодно, схему придумаешь потом. Идиллия держится ровно до первого вызова jsonb_array_length на объекте или до попытки умножить сумму на «скидку», которая внезапно приехала строкой "10%". PostgreSQL в таких местах не прощает — он бросает ошибку и роняет весь запрос. jsonb_typeof возвращает тип JSON-значения обычным текстом, и это даёт вам шанс развести логику по веткам до того, как движок споткнётся.

Что возвращает jsonb_typeof

Функция принимает одно jsonb-значение и отдаёт строку: object, array, string, number, boolean или null. Шесть слов — и вы знаете, с чем имеете дело. Для примеров возьмём таблицу users(id, email, name, country, created_at) с дополнительной колонкой profile jsonb, куда внешний сервис щедро сыплет произвольные поля.

SELECT
  u.id,
  jsonb_typeof(u.profile -> 'tags')    AS tags_type,
  jsonb_typeof(u.profile -> 'age')     AS age_type,
  jsonb_typeof(u.profile -> 'address') AS address_type
FROM users u;

Что здесь важно удержать в голове:

• -> достаёт вложенный jsonb, тогда как ->> сразу приводит результат к text; для jsonb_typeof годится только форма со стрелкой ->.
• Нет ключа в объекте — -> отдаёт SQL NULL, и jsonb_typeof(NULL) тоже вернёт SQL NULL, а вовсе не строку 'null'.
• Строка 'null' всплывает единственным образом: когда в JSON реально лежит литерал null.

Эта развилка между «ключа нет» и «ключ есть, но в нём null» — не педантизм, а фундамент всего дальнейшего. Держите её в уме, к ней мы вернёмся.

Защита jsonb_array_length и арифметики

jsonb_array_length падает с cannot get array length of a non-array, стоит передать ему не массив. А в полуструктурированных данных tags запросто прилетит строкой, числом или вовсе не прилетит. Поэтому спрашиваем тип заранее и лезем за длиной, только когда уверены:

SELECT
  u.id,
  CASE
    WHEN jsonb_typeof(u.profile -> 'tags') = 'array'
      THEN jsonb_array_length(u.profile -> 'tags')
    ELSE 0
  END AS tag_count
FROM users u;

С числами та же история, только цена ошибки выше — обычно это деньги. Допустим, в orders(id, user_id, amount, status, created_at) появилась колонка meta jsonb, и скидка там живёт двойной жизнью: иногда честное число, иногда строка "10%".

SELECT
  o.id,
  o.amount,
  CASE
    WHEN jsonb_typeof(o.meta -> 'discount') = 'number'
      THEN o.amount * (1 - (o.meta ->> 'discount')::numeric)
    ELSE o.amount
  END AS net_amount
FROM orders o;

Уберите проверку — и (o.meta ->> 'discount')::numeric на значении "10%" выбросит ошибку приведения. Причём заденет не одну строку: весь запрос отвалится целиком, и сводка по выручке за день уйдёт в никуда из-за одной кривой записи. Один CASE превращает мину замедленного действия в управляемую ветку.

JSON null против SQL NULL

А вот и обещанная развилка — самая частая ловушка во всей работе с JSON. У JSON есть собственный null, и он принципиально не то же самое, что отсутствие ключа.

SELECT
  u.id,
  u.profile -> 'phone'               AS raw_value,
  jsonb_typeof(u.profile -> 'phone') AS typ,
  (u.profile -> 'phone') IS NULL     AS sql_is_null
FROM users u;

Разложим по полочкам три исхода для ключа phone:

• Ключа нет вовсе: raw_value = SQL NULL, typ = SQL NULL, sql_is_null = true.
• В JSON лежит null: raw_value = 'null'::jsonb, typ = 'null', sql_is_null = false.
• В JSON строка "+7...": typ = 'string'.

Грабли: (u.profile -> 'phone') IS NULL истинно ТОЛЬКО при отсутствующем ключе. Если в JSON сидит литерал null, выражение ложно — хотя по сути «телефона нет». Наивная проверка на IS NULL молча пропустит половину «пустых» телефонов. Чтобы накрыть оба случая разом, проверяйте jsonb_typeof(...) IS DISTINCT FROM 'string' или явно ловите = 'null'.

Валидация полуструктурированного ввода

jsonb_typeof отлично ложится в CHECK-ограничения и в фильтры качества данных — там, где хочется поймать мусор до того, как он расползётся по витринам. Пусть таблица employees(id, name, manager_id, dept, salary) обзавелась колонкой payload jsonb, и нам нужно гарантировать: skills — это массив, а level — число.

ALTER TABLE employees
  ADD CONSTRAINT payload_skills_is_array
  CHECK (
    payload -> 'skills' IS NULL
    OR jsonb_typeof(payload -> 'skills') = 'array'
  );

SELECT id, name
FROM employees
WHERE jsonb_typeof(payload -> 'level') IS DISTINCT FROM 'number';

Второй запрос вытаскивает «битые» строки, где level приехал не числом, — и заодно те, где он оказался JSON null или ключа не было совсем.

Как с этим у других движков:

• MySQL зовёт JSON_TYPE(col->'$.path') и возвращает свой набор меток: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, BOOLEAN, NULL. Числа здесь дробятся на целые и дробные — детализация, которой в PostgreSQL нет.
• ClickHouse исторически разбирал JSON функциями вроде JSONType(json, 'key') (расширение на базе simdjson), а позже завёл отдельный тип JSON; единого аналога jsonb_typeof нет, и поведение пляшет от версии.

Отдельно стоит оценить, почему в паре с jsonb_typeof так хорош именно IS DISTINCT FROM. Обычное сравнение = 'number' на отсутствующем ключе вернёт NULL, то есть «не истину», и строка тихо выпадет из выборки — а вам как раз нужно её поймать. IS DISTINCT FROM относится к NULL как к полноценному значению и честно тянет такие строки в отчёт о качестве. Мелочь, на которой регулярно подрываются.

Итог простой: jsonb_typeof — дешёвый предохранитель, который окупается с первого инцидента. Один CASE или CHECK уберегает запрос от падения на грязном JSON, делает разницу между JSON null и SQL NULL явной и переводит молчаливые ошибки приведения в осознанные ветки логики. Дёшево ставить, дорого не иметь.