JSONB Key-Existence Operators in PostgreSQL: ?, ?| and ?&

Тип jsonb в PostgreSQL даёт три оператора, которые отвечают строго на вопрос «есть ли в объекте такой ключ»: ? (есть ли ключ), ?| (есть ли хоть один из перечисленных) и ?& (есть ли все сразу). Они проверяют только факт существования ключа, ничего не говорят о его значении и при подходящем индексе отрабатывают почти мгновенно. Это рабочая идиома, когда в колонке лежат настройки или фичефлаги и нужно отфильтровать строки по наличию ключа, а не по тому, что под ним записано.

Важно сразу развести две вещи, которые легко спутать: наличие ключа и значение внутри него. Пустой объект, булево false и выключенный флаг — разные состояния, и ? видит только ключ, а не смысл значения. Поэтому оператор существования уместен ровно тогда, когда вопрос действительно про ключ; если же нужно сравнить значение, понадобится извлечь его через ->> и привести к нужному типу.

Три оператора и их семантика

Пусть у таблицы users есть колонка prefs jsonb с настройками и фичефлагами. Базовые проверки читаются почти как обычный текст:

-- has the key "email" at the top level
SELECT id, email
FROM users
WHERE prefs ? 'email';

-- has at least one of these keys
SELECT id
FROM users
WHERE prefs ?| array['newsletter', 'sms'];

-- has all of these keys
SELECT id
FROM users
WHERE prefs ?& array['email', 'phone'];

Запомните три вещи, и недопониманий не будет:

• ? сличает строку с ключами объекта или с элементами массива строк.
• ?| и ?& ждут на входе text[] — обычный SQL-массив строк, а не JSON-массив.
• В jsonb все три оператора смотрят только на верхний уровень. Вложенные ключи для них невидимы.

Последний пункт — самый коварный, поэтому остановимся на нём отдельно.

Только верхний уровень

Оператор ? не «проваливается» внутрь объекта — он перебирает лишь ключи самого верхнего слоя:

-- prefs = {"notify": {"email": true}}
SELECT prefs ? 'email';   -- false: "email" is nested, not top level
SELECT prefs ? 'notify';  -- true: "notify" is a top-level key

Хотите добраться до вложенного ключа — сначала спуститесь на нужный уровень оператором ->:

SELECT (prefs -> 'notify') ? 'email';  -- true

И вот здесь притаились грабли. -> возвращает jsonb, но если ключа notify в объекте нет, он отдаёт не ошибку, а тихий NULL. Дальше NULL ? 'email' тоже сворачивается в NULL — не в false, — и строка просто выпадает из выборки. Если вы рассчитывали, что такие записи попадут в результат, придётся явно подстелить coalesce или отдельную проверку на наличие notify.

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

Классическая задача — выяснить, включён ли у пользователя признак. Когда флаги хранятся ключами объекта, проверка умещается в одну строку:

-- prefs.flags = {"beta_ui": true, "dark_mode": true}
SELECT u.id, u.email
FROM users u
WHERE (u.prefs -> 'flags') ? 'beta_ui';

Только не путайте «ключ присутствует» и «значение истинно» — это разные вопросы, а ? отвечает строго на первый:

-- key present but value is false -> still matches "?"
-- prefs.flags = {"beta_ui": false}
SELECT (prefs -> 'flags') ? 'beta_ui';        -- true (key exists)
SELECT (prefs -> 'flags' ->> 'beta_ui')::bool; -- false (the value)

Иначе говоря, выключенный флаг со значением false оператор ? всё равно посчитает существующим. Если флаги, наоборот, сложены массивом строк, тот же ? срабатывает как проверка «содержит элемент»:

-- prefs.features = ["beta_ui", "dark_mode"]
SELECT (prefs -> 'features') ? 'beta_ui';  -- true

Разгон через GIN-индекс

Самое приятное: ?, ?| и ?& дружат с GIN-индексом, поэтому проверки существования спокойно масштабируются на большие таблицы. Класс операторов по умолчанию (jsonb_ops) одним индексом покрывает и эту тройку, и оператор @>:

CREATE INDEX idx_users_prefs ON users USING gin (prefs);

-- uses the GIN index
SELECT id FROM users WHERE prefs ? 'email';
SELECT id FROM users WHERE prefs ?& array['email', 'phone'];

Пара нюансов, о которые легко споткнуться:

• GIN индексирует ключи верхнего уровня. Выражение (prefs -> 'flags') ? 'beta_ui' обычный GIN по prefs не ускорит — под него нужен отдельный индекс по выражению (prefs -> 'flags').
• Класс jsonb_path_ops компактнее и быстрее на @>, но операторы ?, ?|, ?& он не поддерживает. Нужны проверки существования — оставайтесь на jsonb_ops.

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

А вот и самая болезненная неприятность, и она вообще не про SQL. Знак ? сталкивается лбами с плейсхолдерами: многие драйверы (JDBC, ODBC, ряд библиотек) видят ? и принимают его за маркер параметра. В итоге запрос разваливается ещё на стороне приложения — до базы он попросту не доезжает.

Обходных путей два:

• В PostgreSQL переходите на нумерованные плейсхолдеры $1, $2 — тогда драйвер не путает связываемый параметр с литеральным оператором ?.
• Или меняйте оператор на функцию-синоним: jsonb_exists(prefs, 'email') вместо ?, jsonb_exists_any(...) вместо ?|, jsonb_exists_all(...) вместо ?&. В именах функций знака ? нет, поэтому они безопасны для любого драйвера.

-- equivalent to: prefs ? 'email'
SELECT id FROM users WHERE jsonb_exists(prefs, 'email');

-- equivalent to: prefs ?| array['newsletter', 'sms']
SELECT id FROM users WHERE jsonb_exists_any(prefs, array['newsletter', 'sms']);

Напоследок про соседей. В MySQL такого оператора нет вовсе — наличие пути там проверяют функцией JSON_CONTAINS_PATH(prefs, 'one', '$.email'). В ClickHouse в ходу функции вроде JSONHas(prefs, 'email'). А ?, ?| и ?& — это родная идиома именно PostgreSQL: в связке с GIN-индексом они дают предельно дешёвую проверку существования ключей, за которую не стыдно даже на горячем пути.