jsonb_typeof в PostgreSQL: как проверить тип JSON-значения и не уронить запрос

jsonb в PostgreSQL очень удобен, когда данные приходят в свободной форме. Сегодня внешний сервис прислал возраст числом, завтра — строкой, послезавтра добавил массив тегов, а потом внезапно положил вместо массива объект.

На первый взгляд это гибкость. Можно не менять схему таблицы под каждое новое поле и спокойно складывать данные в одну колонку.

Но у этой свободы есть цена.

Если вызвать jsonb_array_length на объекте, PostgreSQL выбросит ошибку.

Если попытаться привести строку "10%" к числу, PostgreSQL тоже выбросит ошибку.

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

Чтобы не идти по минному полю вслепую, в PostgreSQL есть функция jsonb_typeof. Она показывает тип JSON-значения обычным текстом. Благодаря этому можно сначала проверить, что перед нами массив, число или строка, и только потом выполнять опасное действие.

Проще говоря, jsonb_typeof — это предохранитель для работы с грязным JSON.

Что делает jsonb_typeof

Функция jsonb_typeof принимает значение типа jsonb и возвращает его JSON-тип.

Возможные результаты:

Результат	Что означает
object	объект
array	массив
string	строка
number	число
boolean	логическое значение
null	JSON-значение null

Например:

SELECT jsonb_typeof('{"name": "Alice"}'::jsonb);

Результат:

object

Массив:

SELECT jsonb_typeof('["sql", "postgres"]'::jsonb);

Результат:

array

Строка:

SELECT jsonb_typeof('"hello"'::jsonb);

Результат:

string

Число:

SELECT jsonb_typeof('42'::jsonb);

Результат:

number

Логическое значение:

SELECT jsonb_typeof('true'::jsonb);

Результат:

boolean

JSON-значение null:

SELECT jsonb_typeof('null'::jsonb);

Результат:

null

Здесь важно не перепутать: результат null в последнем примере — это текстовая метка JSON-типа. Это не то же самое, что SQL-значение NULL.

К этой разнице мы ещё вернёмся, потому что она очень важна.

Пример с профилем пользователя

Допустим, есть таблица users:

CREATE TABLE users (
    id bigint,
    email text,
    name text,
    country text,
    created_at timestamp,
    profile jsonb
);

В колонке profile внешний сервис хранит дополнительные данные о пользователе:

{
  "age": 31,
  "tags": ["vip", "newsletter"],
  "address": {
    "city": "Berlin"
  },
  "phone": null
}

Посмотрим, какие типы лежат внутри:

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,
    jsonb_typeof(u.profile -> 'phone') AS phone_type
FROM users u;

Результат может быть таким:

tags_type    array
age_type     number
address_type object
phone_type   null

Функция не достаёт значение «по смыслу». Она просто отвечает на вопрос: какой JSON-тип лежит в этом месте?

Почему нужно использовать ->, а не ->>

В PostgreSQL есть два похожих оператора для работы с JSON:

Оператор	Что возвращает
->	значение как jsonb
->>	значение как текст

Для jsonb_typeof нужен именно jsonb, поэтому используйте ->.

Правильно:

SELECT jsonb_typeof(profile -> 'age') AS age_type
FROM users;

Неправильно:

SELECT jsonb_typeof(profile ->> 'age') AS age_type
FROM users;

Почему второй вариант плохой? Потому что ->> уже превратил значение в текст. А jsonb_typeof работает с JSON-значением, а не с обычной строкой.

Простое правило:

если хотите проверить JSON-тип, доставайте значение через ->.

Если ключа нет

Допустим, в profile нет ключа phone.

{
  "age": 31,
  "tags": ["vip", "newsletter"]
}

Тогда выражение:

SELECT profile -> 'phone'
FROM users;

вернёт SQL NULL.

И функция:

SELECT jsonb_typeof(profile -> 'phone')
FROM users;

тоже вернёт SQL NULL.

Это не строка null. Это именно отсутствие результата на уровне SQL.

А если ключ есть, но внутри лежит JSON null, ситуация другая:

{
  "phone": null
}

Тогда:

SELECT jsonb_typeof(profile -> 'phone')
FROM users;

вернёт текст null.

Получается важное различие:

Ситуация	profile -> 'phone'	jsonb_typeof(...)
Ключа нет	SQL NULL	SQL NULL
Ключ есть, значение JSON null	JSON null	null
Ключ есть, значение строка	JSON-строка	string

Для новичка это один из самых скользких моментов во всём jsonb.

JSON null и SQL NULL — разные вещи

В SQL есть NULL. Он означает отсутствие значения или неизвестное значение.

В JSON тоже есть null. Но это значение внутри JSON-документа.

Снаружи они похожи, но для PostgreSQL это разные ситуации.

Посмотрим на запрос:

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

Возможны три случая.

Первый: ключа phone нет.

{
  "name": "Alice"
}

Тогда:

raw_value     NULL
value_type    NULL
sql_is_null   true

Второй: ключ есть, но в JSON лежит null.

{
  "phone": null
}

Тогда:

raw_value     null
value_type    null
sql_is_null   false

Третий: ключ есть, и там строка.

{
  "phone": "+12025550123"
}

Тогда:

raw_value     "+12025550123"
value_type    string
sql_is_null   false

Главная ловушка здесь такая:

SELECT id
FROM users
WHERE profile -> 'phone' IS NULL;

Такой запрос найдёт строки, где ключа phone нет. Но он не найдёт строки, где ключ есть, а внутри лежит JSON null.

Если для бизнеса обе ситуации означают «телефона нет», нужно проверять обе.

Например:

SELECT id
FROM users
WHERE profile -> 'phone' IS NULL
   OR jsonb_typeof(profile -> 'phone') = 'null';

Или можно искать только нормальные строки с телефоном:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'phone') = 'string';

Если нужен отчёт о проблемных телефонах, удобно использовать IS DISTINCT FROM.

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'phone') IS DISTINCT FROM 'string';

Этот запрос найдёт всё, что не является строкой: отсутствующий ключ, JSON null, число, объект, массив и любые другие неподходящие варианты.

Зачем нужен IS DISTINCT FROM

Обычное сравнение в SQL плохо дружит с NULL.

Например:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'age') <> 'number';

Кажется, что этот запрос найдёт всех пользователей, у которых age не число.

Но если ключа age нет, jsonb_typeof вернёт SQL NULL.

А сравнение:

NULL <> 'number'

не даёт true. Оно даёт неизвестный результат, и строка не попадёт в выборку.

То есть отсутствующий age тихо выпадет из отчёта, хотя вы, возможно, хотели его поймать.

Для таких проверок лучше использовать IS DISTINCT FROM.

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'age') IS DISTINCT FROM 'number';

Этот запрос честно найдёт строки, где age:

• отсутствует;
• равен JSON null;
• является строкой;
• является массивом;
• является объектом;
• является boolean;
• является любым типом, кроме числа.

IS DISTINCT FROM относится к SQL NULL как к обычному сравнимому значению. Поэтому для проверок качества данных он часто удобнее, чем обычное <>.

Защита jsonb_array_length

Функция jsonb_array_length считает длину JSON-массива.

Например:

SELECT jsonb_array_length('["sql", "postgres", "jsonb"]'::jsonb);

Результат:

3

Но если передать ей не массив, запрос упадёт:

SELECT jsonb_array_length('{"tag": "sql"}'::jsonb);

PostgreSQL не скажет: «Длина объекта равна нулю». Он выбросит ошибку, потому что объект — это не массив.

В реальных данных такое случается часто. Например, вы ожидаете, что profile.tags — это массив:

{
  "tags": ["vip", "newsletter"]
}

Но где-то приезжает строка:

{
  "tags": "vip"
}

Или объект:

{
  "tags": {
    "main": "vip"
  }
}

Чтобы запрос не падал, сначала проверяем тип:

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;

Теперь логика безопасная:

• если tags — массив, считаем длину;
• если это не массив, возвращаем 0;
• если ключа нет, тоже возвращаем 0.

Один CASE превращает потенциальное падение запроса в понятное бизнес-правило.

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

С числами ситуация ещё опаснее, потому что чаще всего это деньги, скидки, баллы, лимиты или комиссии.

Допустим, есть таблица orders:

CREATE TABLE orders (
    id bigint,
    user_id bigint,
    amount numeric,
    status text,
    created_at timestamp,
    meta jsonb
);

В колонке meta хранится скидка:

{
  "discount": 0.1
}

Если скидка действительно число, можно посчитать сумму после скидки:

SELECT
    o.id,
    o.amount,
    o.amount * (1 - (o.meta ->> 'discount')::numeric) AS net_amount
FROM orders o;

Но в грязных данных может приехать так:

{
  "discount": "10%"
}

Теперь приведение:

(o.meta ->> 'discount')::numeric

выбросит ошибку. Из-за одной плохой записи упадёт весь отчёт.

Правильнее сначала проверить тип:

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;

Теперь запрос безопасен:

• если discount — число, применяем скидку;
• если скидка отсутствует, равна JSON null, строка или объект, возвращаем исходную сумму.

Это не просто техническая защита. Это явное правило обработки данных.

Как найти грязные строки

Иногда нужно не молча подставлять значение по умолчанию, а найти все строки, где данные приехали не так, как ожидалось.

Например, скидка должна быть числом.

SELECT
    o.id,
    o.meta -> 'discount' AS discount_value,
    jsonb_typeof(o.meta -> 'discount') AS discount_type
FROM orders o
WHERE jsonb_typeof(o.meta -> 'discount') IS DISTINCT FROM 'number';

Такой запрос покажет всё подозрительное:

• скидки строкой;
• скидки объектом;
• скидки массивом;
• JSON null;
• отсутствующий ключ.

После этого можно решить, что делать: исправить данные, отфильтровать их, отправить в отдельный отчёт или договориться с внешней системой о формате.

Проверка нескольких полей сразу

Допустим, в profile ожидается такая структура:

{
  "age": 31,
  "tags": ["vip", "newsletter"],
  "active": true
}

Проверим, что типы соответствуют ожиданиям:

SELECT
    id,
    jsonb_typeof(profile -> 'age') AS age_type,
    jsonb_typeof(profile -> 'tags') AS tags_type,
    jsonb_typeof(profile -> 'active') AS active_type
FROM users
WHERE jsonb_typeof(profile -> 'age') IS DISTINCT FROM 'number'
   OR jsonb_typeof(profile -> 'tags') IS DISTINCT FROM 'array'
   OR jsonb_typeof(profile -> 'active') IS DISTINCT FROM 'boolean';

Такой запрос удобен как контроль качества после импорта.

Если он вернул строки, значит в данных есть отклонения от ожидаемой структуры.

CHECK-ограничение для jsonb

jsonb_typeof можно использовать не только в SELECT, но и в ограничениях таблицы.

Например, у сотрудников есть колонка payload, где поле skills должно быть массивом.

CREATE TABLE employees (
    id bigint,
    name text,
    manager_id bigint,
    dept text,
    salary numeric,
    payload jsonb
);

Добавим проверку:

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

Такое ограничение разрешает строки, где:

• ключа skills нет;
• ключ skills есть и содержит массив.

Но оно не пропустит строку, где skills — строка, число, объект или JSON null.

Например, это нормально:

{
  "skills": ["sql", "testing", "api"]
}

А это уже не пройдёт:

{
  "skills": "sql"
}

Так база сама помогает держать качество данных, а не просто хранит всё подряд.

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

Допустим, в payload есть поле level, и оно должно быть числом.

Найдём строки, где это не так:

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

Этот запрос поймает:

• отсутствующий level;
• JSON null;
• строку вместо числа;
• массив;
• объект;
• boolean.

Если поле level обязательно, это хороший запрос для проверки перед миграцией или выгрузкой в витрину.

Если поле необязательное, условие можно сделать мягче:

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

Такой запрос ищет только те строки, где level есть, но тип неправильный.

CASE как безопасная развилка

Работа с JSON часто выглядит так: если тип правильный — берём значение, если нет — подставляем запасной вариант.

Для этого отлично подходит CASE.

Например, хотим получить возраст пользователя числом:

SELECT
    id,
    CASE
        WHEN jsonb_typeof(profile -> 'age') = 'number'
            THEN (profile ->> 'age')::int
        ELSE NULL
    END AS age
FROM users;

Хотим получить город, только если address — объект, а city — строка:

SELECT
    id,
    CASE
        WHEN jsonb_typeof(profile -> 'address') = 'object'
         AND jsonb_typeof(profile -> 'address' -> 'city') = 'string'
            THEN profile -> 'address' ->> 'city'
        ELSE NULL
    END AS city
FROM users;

Здесь мы не лезем внутрь данных вслепую. Сначала проверяем форму, потом достаём значение.

Такой код длиннее, зато он не падает на первой неожиданной строке.

Типичная ошибка: проверили не тем оператором

Новичок часто пишет так:

SELECT jsonb_typeof(profile ->> 'age') AS age_type
FROM users;

Но ->> превращает JSON-значение в текст.

Если вам нужно проверить тип внутри JSON, используйте ->:

SELECT jsonb_typeof(profile -> 'age') AS age_type
FROM users;

А вот когда тип уже проверен и нужно достать значение для расчёта или сравнения, тогда удобно использовать ->>:

SELECT
    id,
    (profile ->> 'age')::int AS age
FROM users
WHERE jsonb_typeof(profile -> 'age') = 'number';

То есть порядок такой:

1. -> для проверки JSON-типа;
2. ->> для получения текста;
3. приведение к нужному SQL-типу.

Типичная ошибка: забыли про JSON null

Допустим, вы хотите найти пользователей без телефона и пишете:

SELECT id
FROM users
WHERE profile -> 'phone' IS NULL;

Запрос найдёт только тех, у кого ключа phone нет.

Но он пропустит такие данные:

{
  "phone": null
}

Если JSON null тоже означает отсутствие телефона, пишите явно:

SELECT id
FROM users
WHERE profile -> 'phone' IS NULL
   OR jsonb_typeof(profile -> 'phone') = 'null';

Или, если правильный телефон должен быть строкой:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'phone') IS DISTINCT FROM 'string';

Второй вариант часто удобнее для проверки качества: он ловит все неподходящие формы сразу.

Типичная ошибка: обычное сравнение вместо IS DISTINCT FROM

Представим, что поле age должно быть числом.

Наивный запрос:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'age') <> 'number';

не найдёт строки, где ключа age нет, потому что сравнение с SQL NULL не работает как обычное неравенство.

Более надёжный вариант:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'age') IS DISTINCT FROM 'number';

Для аудита грязных данных это почти всегда лучше.

Как похожая проверка выглядит в MySQL

В MySQL для похожей задачи есть функция JSON_TYPE.

Пример:

SELECT JSON_TYPE(JSON_EXTRACT(profile, '$.age')) AS age_type
FROM users;

MySQL возвращает свои названия типов, обычно в верхнем регистре: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, BOOLEAN, NULL.

Есть важное отличие: PostgreSQL возвращает общий тип number, а MySQL может различать целые и дробные числа через INTEGER и DOUBLE.

То есть идея похожая, но детали отличаются.

А что в ClickHouse

В ClickHouse работа с JSON зависит от версии, настроек и выбранного подхода к хранению данных.

В одних случаях используют функции для извлечения JSON-полей, в других — отдельный тип JSON. Встречаются функции вроде JSONType, но единого полного аналога jsonb_typeof, который во всех проектах ведёт себя одинаково, лучше не ожидать.

Практический вывод простой: если переносите запросы из PostgreSQL в ClickHouse, не копируйте проверки типов вслепую. Сначала проверьте функции и поведение на вашей версии ClickHouse.

Когда jsonb_typeof особенно полезен

jsonb_typeof нужен там, где JSON приходит из внешнего мира и вы не можете полностью доверять форме данных.

Типичные случаи:

• события из аналитики;
• вебхуки платёжных систем;
• ответы внешних API;
• пользовательские настройки;
• произвольные анкеты;
• импорт из старой системы;
• фичефлаги и эксперименты;
• временная миграция до нормализации схемы.

Если данные строго контролируются и схема стабильна, jsonb_typeof может быть не нужен в каждом запросе.

Но если JSON полуструктурированный, проверка типа часто дешевле, чем разбирать падение отчёта посреди рабочего дня.

Главное

jsonb_typeof возвращает тип JSON-значения в виде текста.

Возможные результаты:

• object;
• array;
• string;
• number;
• boolean;
• null.

Базовый пример:

SELECT jsonb_typeof(profile -> 'tags') AS tags_type
FROM users;

Для проверки типа используйте ->, потому что он возвращает jsonb.

Для извлечения значения как текста используйте ->> уже после проверки:

SELECT
    id,
    (profile ->> 'age')::int AS age
FROM users
WHERE jsonb_typeof(profile -> 'age') = 'number';

jsonb_typeof помогает безопасно работать с функциями вроде jsonb_array_length:

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

Главная ловушка — различать отсутствующий ключ и JSON null.

Если ключа нет, jsonb_typeof вернёт SQL NULL.

Если ключ есть и внутри лежит JSON null, jsonb_typeof вернёт текст null.

Для поиска неправильных типов часто используйте IS DISTINCT FROM, чтобы не потерять строки с отсутствующими ключами:

SELECT id
FROM users
WHERE jsonb_typeof(profile -> 'age') IS DISTINCT FROM 'number';

jsonb_typeof — маленькая функция, но очень полезная. Она не чинит грязные данные сама, зато помогает увидеть их заранее, аккуратно развести логику по веткам и не уронить большой запрос из-за одной кривой JSON-записи.