Бывает, что заранее неизвестна точная форма данных: у одного товара пять атрибутов, у другого пятнадцать, а завтра добавят ещё три. Городить под это десяток колонок или таблицу ключ-значение неудобно. Здесь и пригождается postgresql со встроенной поддержкой JSON: можно хранить полуструктурированный документ прямо в ячейке, искать по нему и даже индексировать. Фактически это NoSQL внутри обычной реляционной СУБД. В этом уроке разберём, чем json отличается от jsonb, какие есть операторы и функции, как работает язык путей jsonpath и как ускорить выборку через GIN-индекс. И, что не менее важно, когда документ лучше разложить на нормальные таблицы.
Как это работает
В PostgreSQL есть два типа данных под документы: json и jsonb. Тип json хранит исходный текст байт в байт - с пробелами, порядком ключей и даже дублями ключей. При каждом обращении его приходится разбирать заново, поэтому он быстрый на запись, но медленный на чтение и поиск.
Тип jsonb (буква b - от binary) при вставке разбирает документ один раз и складывает в компактном двоичном виде. Пробелы теряются, ключи сортируются, дубликаты схлопываются (остаётся последний). Запись чуть дороже, зато любой доступ к полю и сравнение работают без повторного парсинга. На практике в 2026 году почти всегда берут именно jsonb - он индексируется и поддерживает оператор вхождения. Тип json оставляют, только когда нужно сохранить документ ровно как пришёл.
Доступ к содержимому идёт через операторы. Стрелка -> достаёт элемент как json/jsonb (по ключу для объекта или по индексу для массива), а ->> возвращает то же самое, но как text. Разница принципиальная: -> можно продолжать цепочкой дальше вглубь, а ->> даёт готовую строку для сравнения и приведения типов. Оператор #> идёт по пути-массиву сразу на несколько уровней, #>> - то же, но результат текстом.
Для поиска ключевой оператор - вхождение @>. Он проверяет, содержится ли правый документ в левом как поддокумент. Оператор ? спрашивает, есть ли в объекте такой ключ верхнего уровня; ?| и ?& - есть ли любой или все ключи из списка. Именно эти операторы умеет ускорять GIN-индекс, поэтому они и важны.
SQL и примеры
Соберём документ из строк демобазы Авиаперевозки. Поле contact_data в таблице tickets как раз имеет тип jsonb:
Код: Выделить всё
SELECT ticket_no,
passenger_name,
contact_data ->> 'phone' AS phone,
contact_data ->> 'email' AS email
FROM tickets
WHERE contact_data ? 'email'
LIMIT 5;
Конструкторы собирают JSON прямо в запросе. jsonb_build_object лепит объект из пар ключ-значение, а jsonb_agg сворачивает группу строк в массив:
Код: Выделить всё
SELECT f.flight_no,
jsonb_build_object(
'departure', f.departure_airport,
'arrival', f.arrival_airport,
'seats', jsonb_agg(s.seat_no ORDER BY s.seat_no)
) AS flight_doc
FROM flights f
JOIN seats s ON s.aircraft_code = f.aircraft_code
WHERE f.flight_no = 'PG0001'
GROUP BY f.flight_no, f.departure_airport, f.arrival_airport;
Теперь jsonpath - мини-язык запросов внутри документа (появился в PG12, расширялся дальше). Функция jsonb_path_query вынимает значения по выражению, а оператор @@ проверяет предикат:
Код: Выделить всё
SELECT ticket_no
FROM tickets
WHERE contact_data @@ '$.phone starts with "+7"';
Главное про скорость - индекс. Без него @> и ? читают всю таблицу. GIN-индекс по jsonb эти операторы ускоряет:
Код: Выделить всё
CREATE INDEX idx_tickets_contact ON tickets USING gin (contact_data);
SELECT ticket_no
FROM tickets
WHERE contact_data @> '{"phone": "+70127117011"}';
Код: Выделить всё
CREATE INDEX idx_tickets_contact_path
ON tickets USING gin (contact_data jsonb_path_ops);
Код: Выделить всё
CREATE INDEX idx_tickets_email
ON tickets ((contact_data ->> 'email'));
Частые грабли
- Путают -> и ->>. Сравнение contact_data -> 'phone' = '+7...' не сработает: слева jsonb, справа text. Для сравнения нужен ->>.
- Берут json вместо jsonb и потом удивляются, почему нельзя построить GIN-индекс и не работает @>. Индексация и вхождение - это про jsonb.
- Ждут, что GIN ускорит ->> или сортировку по полю. GIN заточен под @> ? ?| ?&, для извлечения одного поля нужен B-tree по выражению.
- Кладут в jsonb то, что строго структурировано и активно джойнится. Документ нельзя связать внешним ключом, а апдейт одного поля переписывает всю ячейку целиком.
- Сравнивают числа как текст: ->> возвращает строку, поэтому '9' окажется больше '100'. Приводите явно через (... ->> 'n')::int.
- Забывают, что jsonb теряет порядок ключей и дубликаты. Если важен исходный байтовый вид документа (например, подпись), нужен тип json.
- Делают GIN-индекс по всему большому документу, когда ищут по одному полю - индекс распухает. Часто выгоднее индекс по выражению.
1. Создайте таблицу: CREATE TABLE products (id int generated always as identity primary key, attrs jsonb);
2. Вставьте 5-6 строк с разным набором ключей, например {"color":"red","size":42,"tags":["sale","new"]} и {"color":"blue","weight":1.2}.
3. Выберите все товары, где attrs @> '{"color":"red"}'. Затем те, где есть ключ weight через attrs ? 'weight'.
4. Достаньте первый тег: attrs #>> '{tags,0}'. Сравните с attrs -> 'tags' -> 0 и поймите разницу типов.
5. Запустите EXPLAIN ANALYZE на запросе с @> до индекса - увидите Seq Scan.
6. Создайте CREATE INDEX ... USING gin (attrs) и повторите EXPLAIN ANALYZE: должен появиться Bitmap Index Scan.
7. Через jsonb_path_query найдите товары с числовым size больше 40: запрос '$ ? (@.size > 40)'.
Контрольные вопросы
- Чем jsonb отличается от json при хранении и при поиске, и почти всегда выбирают именно jsonb?
- В чём разница между -> и ->>, и почему она важна при сравнении значений?
- Какие операторы ускоряет GIN-индекс по jsonb и какой это даёт тип сканирования в плане?
- Когда стоит взять класс jsonb_path_ops, а когда обычный GIN или B-tree по выражению?
- Что делают jsonb_build_object и jsonb_agg, и зачем собирать документ прямо в SQL?
- По каким признакам понять, что данные пора нормализовать в таблицы, а не держать в одном jsonb-поле?
