Какие особенности и проблемы возникают при работе с JSON в PostgreSQL

Что проверяют

Понимаете ли вы разницу между json и jsonb и где заканчивается удобство хранения без схемы. Следом обычно спрашивают, как индексировать поле внутри документа и как маппить такую колонку в Hibernate.

Как отвечать

Начните с выбора типа:

• json и jsonb: json хранит исходный текст с пробелами, порядком ключей и дублями и разбирает его при каждом обращении. jsonb хранит разобранное бинарное представление: запись чуть дороже, операторы быстрее, дубли ключей схлопываются до последнего. По умолчанию берут jsonb;
• операторы: -> возвращает jsonb, ->> текст, @> проверяет вхождение, ? наличие ключа. Для сложных выборок есть JSON path (jsonb_path_query, @?), а в PostgreSQL 17 появился JSON_TABLE;
• индексы: GIN с классом jsonb_ops обслуживает @>, ?, ?|, ?&, класс jsonb_path_ops компактнее, но умеет только вхождение и JSON path. Для фильтра по одному ключу выгоднее B-tree по выражению: CREATE INDEX ON orders ((data->>'status'));
• обновление: jsonb_set не правит документ на месте. Пишется новая версия строки, а большой документ из TOAST переписывается целиком;
• статистика: по ключам внутри jsonb планировщик статистику не собирает и ошибается в оценках. Лечится индексом по выражению или выносом ключа в колонку;
• Java: драйвер не знает типа jsonb, и setString падает с ошибкой column is of type jsonb but expression is of type character varying. Помогает ?::jsonb в запросе, PGobject с типом jsonb или параметр stringtype=unspecified. В Hibernate 6 хватает @JdbcTypeCode(SqlTypes.JSON) на поле.

Где спотыкаются

Складывают в jsonb всё, включая поля для джойнов и обязательные атрибуты. Внешних ключей, NOT NULL и проверки типов там нет, и через год в данных живут три формата одной даты.

Вешают GIN и удивляются, что запрос WHERE data->>'status' = 'paid' его не использует. GIN работает с операторами @> и ?, поэтому запрос переписывают на вхождение или заводят индекс по выражению.

Сравнивают значения как строки: ->> отдаёт текст, и data->>'price' > '100' сравнивает лексикографически. Нужен явный каст к numeric.