Руководство по созданию эффективных запросов

и
ндексирование
 JsoN 
и
 JsoNB
Иногда разработчики в поисках гибкости преобразуют строки таблицы 
в текстовый или слабоструктурированный формат (JSON или XML), а затем 
используют полнотекстовый поиск вместо более точных индексов. Такой 
подход определенно работает лучше, чем внешние инструменты индекси-
рования, но он значительно медленнее, чем точные индексы.
Вернемся к вопросу, которым мы задались в конце главы 13: зачем возить-
ся с созданием функций, которые преобразуют результаты поиска в JSON, 
если можно просто сохранить тип JSON непосредственно в базе данных?
Посмотрим, как такой подход будет работать на практике. Для этого соз-
дадим таблицу, в которой сохраним бронирования в виде объектов JSON. 
Первая проблема, с которой мы столкнемся, состоит в том, что нам могут 
понадобиться разные структуры JSON для разных точек входа приложения 
(мы уже создали несколько различных типов записей в главах 11–13). Но 
предположим, что можно объединить различные требования и хранить дан-
ные таким образом, чтобы это удовлетворяло большинству сценариев ис-
пользования. Тогда мы можем взять код, представленный в лис тинге 14.1.
Обратите внимание, что мы создаем таблицу с типом столбца JSONB (JSON 
Binary), а не JSON. Единственная разница между этими типами заключается 
в том, что JSONB хранит двоичное представление данных JSON, а не стро-
ку. Для типа JSON можно создать только индексы на основе B-деревьев по 
определенным тегам, а для этого нужно указать полный путь в JSON, включая 

---

266

Более сложная фильтрация и поиск
индексы массивов, так что проиндексировать «любой» сегмент бронирова-
ния не получится.
Листинг 14.1 

Создание таблицы с типом столбца JSONB
-- упрощенный тип для сегмента бронирования
CREATE TYPE booking_leg_record_2 AS (
booking_leg_id integer,
leg_num integer,
booking_id integer,
flight flight_record
);
-- упрощенный тип для бронирования
CREATE TYPE booking_record_2 AS (
booking_id integer,
booking_ref text,
booking_name text,
email text,
account_id integer,
booking_legs booking_leg_record_2[],
passengers passenger_record[]
);
-- таблица
CREATE TABLE booking_jsonb AS
SELECT b.booking_id,
to_jsonb( row (
b.booking_id,
b.booking_ref,
b.booking_name,
b.email,
b.account_id,
ls.legs,
ps.passengers
)::booking_record_2
) AS cplx_booking
FROM booking b
JOIN ( SELECT booking_id,
array_agg( row (
booking_leg_id,
leg_num,
booking_id,
row (
f.flight_id,
flight_no,
departure_airport,
dep.airport_name,
arrival_airport,
arv.airport_name,
scheduled_departure,
scheduled_arrival
)::flight_record
)::booking_leg_record_2) legs

---

Индексирование JSON и JSONB 

267
FROM booking_leg l
JOIN flight f ON f.flight_id = l.flight_id
JOIN airport dep ON dep.airport_code = f.departure_airport
JOIN airport arv ON arv.airport_code = f.arrival_airport
GROUP BY booking_id
) ls ON b.booking_id = ls.booking_id
JOIN ( SELECT booking_id,
array_agg( row(
passenger_id,
booking_id,
passenger_no,
last_name,
first_name
)::passenger_record) AS passengers
FROM passenger
GROUP BY booking_id
) ps ON ls.booking_id = ps.booking_id
;
Если мы хотим иметь высокопроизводительные индексы для столбцов 
JSON, необходимо использовать тип JSONB.
Создание таблицы займет некоторое время. Индекс GIN тоже строится не 
мгновенно:
CREATE INDEX idxgin ON booking_jsonb USING GIN (cplx_booking);
Но после того, как индекс создан, кажется, что в мире уже не осталось не-
решенных проблем. Теперь мы можем получить все необходимые данные без 
каких-либо соединений и построения сложной структуры, используя прос-
тые запросы, подобные тому, что показан в лис тинге 14.2.