Пошук даних у масиві: ANY, ALL, array_contains()

Масиви часто використовують для зберігання кількох значень в одній колонці — це можуть бути теги, номери, ролі чи вподобання. Але як зрозуміти, чи є в масиві потрібне нам значення, або навпаки — чи всі елементи відповідають певній умові? PostgreSQL дає зручні інструменти для таких перевірок: ANY, ALL і array_contains(). Давай розберемо кожен з них по черзі.

Що таке ANY?

Уяви, що в тебе є масив, у якому перелічено кілька значень, наприклад, ID товарів, номери телефонів чи список хобі. Часто виникає задача перевірити, чи є в масиві певне значення. Ось тут на сцену виходить оператор ANY. Він дозволяє перевірити, чи містить наш "шукана штука" масив.

Синтаксис ANY

SELECT *
FROM your_table
WHERE value = ANY(array_column);

Тут:

• array_column — це масив, який ми перевіряємо.
• value — це значення, яке ми шукаємо у масиві.

Приклад використання ANY

Припустимо, у нас є таблиця students з такою структурою:

Нас цікавлять студенти, у яких серед хобі є плавання. Запит буде виглядати так:

SELECT *
FROM students
WHERE 'плавання' = ANY(hobbies);

Результат:

Коротко кажучи, ANY перевіряє: "Чи є в цьому масиві це значення?".

Довіряй, але перевіряй усе! Використовуємо ALL

Тепер уяви зворотну ситуацію: треба впевнитися, що всі елементи масиву відповідають певному правилу. Для цього PostgreSQL пропонує оператор ALL. З його допомогою можна перевірити, чи всі елементи масиву, наприклад, дорівнюють заданому значенню, більші за певне число або відповідають будь-якій іншій логічній умові.

Синтаксис ALL

SELECT *
FROM your_table
WHERE value < ALL(array_column);

Тут:

• array_column — це масив, який ми перевіряємо.
• value < ALL(...) означає, що всі елементи масиву мають бути більші за значення.

Повернемося до нашої таблиці students. Припустимо, ми хочемо знайти студентів, у яких всі хобі починаються з букви "ч". Запит:

SELECT *
FROM students
WHERE 'читання' = ALL(hobbies);

Результат буде порожнім, бо ні в кого всі хобі не дорівнюють слову "читання". Щоб приклад був зрозумілішим (і працював), давай подумаємо про числові дані.

Припустимо, є таблиця orders:

Знайти всі замовлення, де кожна позиція дешевша за 400:

SELECT *
FROM orders
WHERE 400 > ALL(prices);

Результат:

Фільтрація рядків за вмістом масиву

Ми розібрали базові функції та оператори для пошуку даних у масивах, але як вони застосовуються на практиці? Давай розглянемо кілька прикладів використання.

Приклад 1: Знайти студентів, у яких хоча б одне хобі — музика

Початкова таблиця:

SELECT *
FROM students
WHERE 'музика' = ANY(hobbies);

Результат:

Приклад 2: Знайти замовлення з цінами більше 100 у всіх позиціях

Початкова таблиця orders:

SELECT *
FROM orders
WHERE 100 < ALL(prices);

Результат:

Типові помилки та як їх уникнути

Коли ти працюєш із масивами, особливо для пошуку даних, є кілька підводних каменів, які можуть тебе підстерігати.

Помилка: Неправильне використання ANY або ALL. Наприклад, якщо ти випадково переплутаєш оператори, можна отримати неочікувані результати.

-- Невірний варіант:
SELECT *
FROM students
WHERE hobbies = ANY('читання');

У цьому прикладі помилка в тому, що hobbies — це масив, а 'читання' — рядок. Саме масив має перевірятися через ANY, а не навпаки.

Помилка: Індексація масивів починається з 1, а не з 0. Якщо ти використовуєш масиви в інших функціях або намагаєшся витягти елементи вручну, враховуй особливості PostgreSQL.

Помилка: Відсутність індексації. Якщо масиви використовуються для частих операцій пошуку (ANY, ALL), індексування може суттєво прискорити запити.