Как проверять наличие ключей в jsonb в PostgreSQL: операторы ?, ?| и ?&

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

Для таких случаев в PostgreSQL есть три оператора, которые отвечают на очень простой вопрос: есть ли такой ключ?

• ? — есть ли один конкретный ключ;
• ?| — есть ли хотя бы один ключ из списка;
• ?& — есть ли все ключи из списка.

Главное: эти операторы проверяют только факт существования ключа. Они не смотрят, что внутри: true, false, пустая строка, число, объект или null.

Представьте шкаф с ящиками. Оператор ? спрашивает: «Есть ли ящик с наклейкой email?» Он не открывает ящик и не оценивает содержимое. Если ящик есть, ответ — да.

Почему это важно

В jsonb легко перепутать два разных вопроса:

1. Есть ли ключ?
2. Какое значение лежит по этому ключу?

Например, вот настройки пользователя:

{
  "email": false,
  "phone": true
}

Ключ email здесь есть. Но значение у него — false.

Для оператора ? это всё равно «ключ найден». Он не считает флаг включённым или выключенным. Он просто видит, что в объекте есть поле с таким именем.

Это особенно важно для фичефлагов. Если флаг хранится так:

{
  "beta_ui": false
}

то проверка наличия ключа покажет true, хотя сам флаг выключен.

Базовый пример с настройками пользователя

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

CREATE TABLE users (
    id bigint,
    email text,
    prefs jsonb
);

В колонке prefs лежат настройки:

{
  "email": true,
  "phone": true,
  "newsletter": false
}

Чтобы найти пользователей, у которых в настройках есть ключ email, пишем:

SELECT id, email
FROM users
WHERE prefs ? 'email';

Запрос читается почти по-человечески: выбрать пользователей, у которых prefs содержит ключ email.

Оператор ?: есть один ключ

Оператор ? проверяет наличие одного ключа на верхнем уровне объекта:

SELECT id, email
FROM users
WHERE prefs ? 'email';

Он вернёт строки, где в prefs есть ключ email.

Подходящие данные:

{
  "email": true,
  "phone": true
}

{
  "email": false
}

{
  "email": null
}

Во всех трёх случаях ключ email существует. Значение разное, но для ? это не важно.

Не подойдут данные, где такого ключа нет:

{
  "phone": true,
  "newsletter": true
}

Оператор ?|: есть хотя бы один ключ из списка

Иногда нужно проверить не один ключ, а несколько вариантов.

Например: пользователь подходит, если у него есть хотя бы один канал связи — email или phone.

SELECT id
FROM users
WHERE prefs ?| array['email', 'phone'];

Оператор ?| означает: найди строки, где есть хотя бы один из перечисленных ключей.

Подойдут такие объекты:

{
  "email": true
}

{
  "phone": true
}

{
  "email": true,
  "phone": true
}

Не подойдёт объект без обоих ключей:

{
  "telegram": true
}

Обратите внимание: справа используется обычный SQL-массив строк — array['email', 'phone']. Это не JSON-массив.

Оператор ?&: есть все ключи сразу

Если нужно проверить, что присутствуют все перечисленные ключи, используется ?&.

Например: нам нужны пользователи, у которых заполнены и email, и phone.

SELECT id
FROM users
WHERE prefs ?& array['email', 'phone'];

Подойдёт только такой вариант:

{
  "email": true,
  "phone": true
}

А вот эти объекты не подойдут:

{
  "email": true
}

{
  "phone": true
}

Для ?& важно, чтобы были все ключи из списка.

Короткая шпаргалка по трём операторам

Оператор	Что проверяет	Пример
?	Есть один ключ	prefs ? 'email'
`?	`	Есть хотя бы один ключ из списка
?&	Есть все ключи из списка	prefs ?& array['email', 'phone']

Запомнить можно так:

? — один вопрос.

?| — вопрос с «или».

?& — вопрос с «и».

Операторы смотрят только на верхний уровень

Самая частая ловушка: ? не ищет ключи в глубине JSON.

Допустим, в prefs лежит такой объект:

{
  "notify": {
    "email": true
  }
}

Ключ email здесь есть, но он вложен внутрь notify.

Проверка даст такой результат:

SELECT prefs ? 'email'
FROM users;

Результат будет false, потому что на верхнем уровне есть только ключ notify.

А вот так будет true:

SELECT prefs ? 'notify'
FROM users;

Потому что notify — это ключ верхнего уровня.

Как проверить вложенный ключ

Чтобы проверить вложенный ключ, сначала нужно спуститься на нужный уровень через ->, а потом уже применить ?.

SELECT (prefs -> 'notify') ? 'email'
FROM users;

Здесь логика такая:

1. prefs -> 'notify' достаёт вложенный объект;
2. ? 'email' проверяет, есть ли в этом объекте ключ email.

То есть мы сначала открыли ящик notify, а уже внутри него посмотрели, есть ли отделение email.

Что будет, если вложенного объекта нет

Допустим, у части пользователей в prefs вообще нет ключа notify.

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

prefs -> 'notify'

вернёт NULL.

И проверка:

(prefs -> 'notify') ? 'email'

тоже не станет false в привычном бытовом смысле. Она даст NULL.

В WHERE это обычно ведёт себя нормально: строка просто не попадёт в результат.

SELECT id
FROM users
WHERE (prefs -> 'notify') ? 'email';

Этот запрос вернёт только тех, у кого есть notify, и внутри него есть email.

Но если вам нужен явный false вместо NULL, можно использовать coalesce:

SELECT id,
       coalesce((prefs -> 'notify') ? 'email', false) AS has_email_notify
FROM users;

Так результат будет проще читать: либо true, либо false.

Проверка фичефлагов

Фичефлаги часто хранят в JSON. Например:

{
  "flags": {
    "beta_ui": true,
    "dark_mode": true
  }
}

Чтобы найти пользователей, у которых есть флаг beta_ui, можно написать:

SELECT u.id, u.email
FROM users u
WHERE (u.prefs -> 'flags') ? 'beta_ui';

Этот запрос проверяет именно наличие ключа beta_ui внутри объекта flags.

Но ещё раз: он не проверяет, что значение равно true.

Вот такой объект тоже пройдёт проверку:

{
  "flags": {
    "beta_ui": false
  }
}

Почему? Потому что ключ beta_ui есть.

Как проверить не наличие, а значение

Если нужно узнать, что флаг действительно включён, нужно извлечь значение и привести его к boolean.

SELECT u.id, u.email
FROM users u
WHERE (u.prefs -> 'flags' ->> 'beta_ui')::boolean = true;

Здесь важно различать два оператора:

• -> достаёт JSON-значение;
• ->> достаёт текст.

Для приведения к boolean удобнее использовать ->>, потому что оно отдаёт обычный текст, который можно превратить в true или false.

Можно записать короче:

SELECT u.id, u.email
FROM users u
WHERE (u.prefs -> 'flags' ->> 'beta_ui')::boolean;

Но для новичка вариант с = true часто понятнее.

Если флаги лежат массивом строк

Иногда фичи хранят не объектом, а массивом:

{
  "features": ["beta_ui", "dark_mode"]
}

Оператор ? умеет работать и с массивом строк. В этом случае он проверяет, есть ли в массиве такой элемент.

SELECT id
FROM users
WHERE (prefs -> 'features') ? 'beta_ui';

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

Но это работает именно для элементов массива строк. Если структура сложнее, лучше явно проверять данные через подходящие JSON-функции или операторы.

Индекс для быстрых проверок

Операторы ?, ?| и ?& особенно полезны на больших таблицах, потому что их можно ускорить с помощью GIN-индекса.

CREATE INDEX idx_users_prefs
ON users
USING gin (prefs);

После этого PostgreSQL сможет быстрее выполнять запросы вроде:

SELECT id
FROM users
WHERE prefs ? 'email';

И такие:

SELECT id
FROM users
WHERE prefs ?& array['email', 'phone'];

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

Важный нюанс про вложенные проверки и индекс

Обычный индекс по всей колонке:

CREATE INDEX idx_users_prefs
ON users
USING gin (prefs);

помогает запросам по самой колонке prefs.

Но если вы часто проверяете вложенный объект:

SELECT id
FROM users
WHERE (prefs -> 'flags') ? 'beta_ui';

то для такого выражения лучше сделать отдельный индекс:

CREATE INDEX idx_users_prefs_flags
ON users
USING gin ((prefs -> 'flags'));

Здесь индекс строится не по всему prefs, а именно по выражению (prefs -> 'flags').

Это полезно, когда фичефлаги живут внутри одного вложенного объекта, и вы часто фильтруете пользователей именно по ним.

jsonb_ops и jsonb_path_ops: не перепутайте

У GIN-индекса для jsonb есть разные классы операторов.

По умолчанию используется jsonb_ops:

CREATE INDEX idx_users_prefs
ON users
USING gin (prefs);

Он поддерживает операторы существования:

• ?;
• ?|;
• ?&.

А ещё он поддерживает проверки через @>.

Есть другой класс — jsonb_path_ops:

CREATE INDEX idx_users_prefs_path
ON users
USING gin (prefs jsonb_path_ops);

Он часто компактнее и хорош для запросов с @>, но операторы ?, ?| и ?& он не поддерживает.

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

если вам нужны проверки существования ключей через ?, ?|, ?&, используйте обычный jsonb_ops.

Ловушка с параметризованными запросами

Есть неприятная практическая проблема: знак ? в SQL может конфликтовать с плейсхолдерами в некоторых драйверах и библиотеках.

Например, многие инструменты используют ? как место для параметра:

SELECT *
FROM users
WHERE email = ?;

А в PostgreSQL ? ещё и оператор для jsonb.

Из-за этого приложение может решить, что prefs ? 'email' — это не оператор, а странный параметризованный запрос. Иногда ошибка возникает ещё до отправки запроса в базу.

Как обойти конфликт с ?

Первый способ — использовать нумерованные параметры PostgreSQL:

SELECT id
FROM users
WHERE prefs ? $1;

И передать значение email как первый параметр.

Второй способ — заменить операторы функциями-синонимами.

Вместо:

SELECT id
FROM users
WHERE prefs ? 'email';

можно написать:

SELECT id
FROM users
WHERE jsonb_exists(prefs, 'email');

Вместо ?|:

SELECT id
FROM users
WHERE jsonb_exists_any(prefs, array['newsletter', 'sms']);

Вместо ?&:

SELECT id
FROM users
WHERE jsonb_exists_all(prefs, array['email', 'phone']);

Эти функции делают то же самое, но в их именах нет символа ?, поэтому драйверы обычно не путаются.

Когда использовать ?, а когда доставать значение

Используйте ?, ?| и ?&, когда вопрос звучит так:

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

Не используйте эти операторы, если вопрос про значение:

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

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

SELECT id
FROM users
WHERE (prefs ->> 'theme') = 'dark';

SELECT id
FROM users
WHERE (prefs ->> 'age')::int >= 18;

SELECT id
FROM users
WHERE (prefs -> 'flags' ->> 'beta_ui')::boolean = true;

А как в других базах

Операторы ?, ?| и ?& — это именно PostgreSQL-идиома.

В MySQL такого синтаксиса нет. Там наличие пути обычно проверяют через JSON_CONTAINS_PATH:

SELECT id
FROM users
WHERE JSON_CONTAINS_PATH(prefs, 'one', '$.email');

В ClickHouse для похожих задач могут использовать функции вроде JSONHas:

SELECT id
FROM users
WHERE JSONHas(prefs, 'email');

То есть идея одна и та же: проверить наличие ключа. Но синтаксис в разных базах отличается.

Главное

?, ?| и ?& — это короткие и выразительные операторы PostgreSQL для проверки существования ключей в jsonb.

? проверяет один ключ:

SELECT id
FROM users
WHERE prefs ? 'email';

?| проверяет, есть ли хотя бы один ключ из списка:

SELECT id
FROM users
WHERE prefs ?| array['email', 'phone'];

?& проверяет, есть ли все ключи из списка:

SELECT id
FROM users
WHERE prefs ?& array['email', 'phone'];

Главное не путать наличие ключа и значение. Ключ с false всё равно существует. Ключ с null тоже существует. Оператор ? не думает за вас, включён флаг или выключен, — он только проверяет, есть ли такое имя в JSON.

Для вложенных объектов сначала спускайтесь через ->, для проверки значений используйте ->>, а для больших таблиц добавляйте GIN-индекс. Тогда проверки по jsonb будут не только удобными, но и быстрыми.