Skip to content

2 Полнотекстовый поиск

Лабораторная работа №6: Работа с JSONB и полнотекстовым поиском в PostgreSQL

Теоретические сведения

1. Тип данных JSONB
  • JSONB (Binary JSON) — бинарное представление JSON, оптимизированное для хранения и запросов. Отличается от типа JSON:
  • Данные хранятся в разобранном бинарном формате.
  • Поддерживает индексацию (GIN, GiST).
  • Удаляет пробелы и дубликаты ключей.
  • Подходит для сложных структур: вложенные объекты, массивы.
  • Операторы для работы с JSONB:
  • ->: Получить значение как JSONB (например, data->'user').
  • ->>: Получить значение как текст (например, data->>'name').
  • @>: Проверить наличие ключа или значения (например, data @> '{"age": 25}').
2. Полнотекстовый поиск
  • tsvector — тип данных, хранящий лексемы текста с позициями.
  • tsquery — тип данных для поисковых запросов.
  • Функции:
  • to_tsvector('язык', текст): Преобразует текст в tsvector.
  • to_tsquery('язык', запрос): Формирует поисковый запрос.
  • Оператор @@: Проверяет соответствие tsvector и tsquery.
  • Индексы GIN:
  • Оптимизируют поиск по JSONB и tsvector.
  • Ускоряют операции с массивами и полнотекстовым поиском.

Примеры выполнения

1. Работа с JSONB
  • Создание таблицы: sql CREATE TABLE products ( id SERIAL PRIMARY KEY, details JSONB );
  • Вставка данных: sql INSERT INTO products (details) VALUES ('{"name": "Ноутбук", "price": 50000, "tags": ["техника", "электроника"], "specs": {"ram": 16, "cpu": "Intel"}}'), ('{"name": "Книга", "price": 300, "tags": ["литература"], "specs": {"pages": 200}}');
  • Поиск по JSONB:
  • Найти товары с тегом "техника": sql SELECT * FROM products WHERE details->'tags' ? 'техника';
  • Найти товары с ценой больше 40000: sql SELECT * FROM products WHERE (details->>'price')::INT > 40000;

  • Найти все книги с сраницами в количестве 300: sql SELECT * FROM products WHERE details #>>'{space,pages}'='300';

  • Индекс для JSONB: sql CREATE INDEX idx_gin_details ON products USING GIN (details);

2. Полнотекстовый поиск
  • Создание таблицы с tsvector: sql CREATE TABLE articles ( id SERIAL PRIMARY KEY, content TEXT, content_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('russian', content)) STORED );
  • Вставка данных: sql INSERT INTO articles (content) VALUES ('PostgreSQL — это объектно-реляционная СУБД с открытым исходным кодом.'), ('Полнотекстовый поиск позволяет искать слова и фразы в тексте.');
  • Поиск по тексту: sql SELECT * FROM articles WHERE content_vector @@ to_tsquery('russian', 'поиск & текст');
  • Индекс для tsvector: sql CREATE INDEX idx_gin_search ON articles USING GIN (content_vector);

Практические задания

Задание 1: Работа с JSONB
  1. Создайте таблицу users с полем profile типа JSONB. Поле должно хранить:
  2. name (строка),
  3. age (число),
  4. hobbies (массив строк),
  5. address (объект с полями city и street).
  6. Вставьте 5 записей с разными данными.
  7. Напишите запросы:
  8. Найти пользователей, у которых в хобби есть "программирование".
  9. Вывести имя и город проживания для пользователей старше 25 лет.
  10. Создайте GIN-индекс для поля profile и проверьте скорость поиска.
Задание 2: Полнотекстовый поиск
  1. Создайте таблицу books с полями:
  2. title (строка),
  3. description (текст),
  4. tsv (tsvector, генерируемый из description).
  5. Вставьте 5 книг с описаниями (например, техническая литература, художественные произведения).
  6. Реализуйте поиск по словам:
  7. Найти все книги, где упоминается "база данных" или "алгоритм".
  8. Найти книги со словом "литература" в описании.
  9. Создайте GIN-индекс для поля tsv и сравните скорость поиска до и после индексации.
Задание 3: Комбинирование JSONB и полнотекстового поиска
  1. Расширьте таблицу products (из примера), добавив поле reviews (JSONB), которое хранит отзывы в формате: json [ {"user": "Иван", "text": "Отличный товар!", "rating": 5}, {"user": "Мария", "text": "Качество среднее", "rating": 3} ]
  2. Напишите запрос:
  3. Найти товары, в отзывах которых есть слово "отличный" (используйте полнотекстовый поиск по полю text внутри reviews).
  4. Дополнительно: Реализуйте поиск по рейтингу отзывов (например, товары с средним рейтингом больше 4).

Контрольные вопросы

  1. Чем JSONB отличается от JSON?
  2. Какой индекс лучше использовать для полнотекстового поиска и почему?
  3. Как выполнить поиск по вложенному полю в JSONB?
  4. Зачем нужно указывать язык в to_tsvector?

Работа позволяет освоить гибридные возможности PostgreSQL, сочетающие реляционные и NoSQL-подходы. Примеры можно усложнить, добавив работу с массивами или более сложными запросами.