Извлечение данных из массива: unnest(), array_length(), array_position()

Работа с массивами не ограничивается их созданием и хранением — часто возникает необходимость извлечь отдельные элементы или проанализировать их содержимое. PostgreSQL предлагает несколько встроенных функций для таких операций. Рассмотрим их по порядку.

Функция unnest(): распаковка массива

Функция unnest() буквально распаковывает массив, превращая его элементы в отдельные строки. Это особенно полезно, если вам нужно работать с данными массива в виде таблицы.

Пример 1: Простая распаковка массива

Допустим, у нас есть массив с названиями факультетов:

SELECT ARRAY['Информатика', 'Математика', 'Физика'] AS факультеты;

Теперь нужно извлечь каждый элемент как отдельную строку. Используем unnest():

SELECT unnest(ARRAY['Информатика', 'Математика', 'Физика']) AS факультет;

Результат:

Пример 2: Распаковка массива в таблице

Предположим, у нас есть таблица courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT,
    tags TEXT[]
);

INSERT INTO courses (course_name, tags)
VALUES
    ('Алгоритмы', ARRAY['Программирование', 'Информатика']),
    ('Линейная алгебра', ARRAY['Математика', 'Алгебра']),
    ('Физика основ', ARRAY['Физика', 'Общая']);

Теперь извлечём все теги из массива:

SELECT course_name, unnest(tags) AS tag
FROM courses;

Результат:

Вы видите, что каждая строка массива превращается в отдельную строку таблицы.

Функция array_length(): определяем размер массива

Второй важной функцией для работы с массивами является array_length(). Она возвращает длину массива (то есть количество элементов) для заданного измерения.

Пример 1: Количество элементов в одномерном массиве

Возьмём массив:

SELECT ARRAY['Яблоко', 'Банан', 'Апельсин'] AS фрукты;

Если мы хотим узнать, сколько фруктов в массиве:

SELECT array_length(ARRAY['Яблоко', 'Банан', 'Апельсин'], 1) AS длина;

Результат:

Здесь 1 указывает на измерение массива. У массивов в PostgreSQL может быть несколько измерений (например, двумерные массивы), но об этом мы поговорим в другом контексте.

Пример 2: Количество элементов в массиве таблицы

Посмотрим, сколько тегов у каждого курса:

SELECT course_name, array_length(tags, 1) AS количество_тегов
FROM courses;

Результат:

Функция просто говорит: «Эй, здесь два элемента!», а это уже многое упрощает в анализе данных.

Функция array_position(): поиск значения в массиве

Теперь, представьте себе ситуацию, что вы ищете определённый элемент в массиве. Функция array_position() приходит на помощь: она возвращает позицию первого вхождения элемента.

Пример 1: Поиск элемента

Допустим, у нас есть массив:

SELECT ARRAY['Красный', 'Синий', 'Зелёный', 'Жёлтый'] AS цвета;

Попробуем найти позицию элемента "Синий":

SELECT array_position(ARRAY['Красный', 'Синий', 'Зелёный', 'Жёлтый'], 'Синий') AS позиция;

Результат:

Если элемент отсутствует, функция возвращает NULL. Давайте проверим:

SELECT array_position(ARRAY['Красный', 'Синий', 'Зелёный', 'Жёлтый'], 'Чёрный') AS позиция;

Результат:

Пример 2: Поиск в массиве таблицы

Вы хотите узнать, у какого курса есть тег "Информатика". Сначала найдем нужные строки:

SELECT course_name, array_position(tags, 'Информатика') AS позиция
FROM courses;

Результат:

Теперь добавьте фильтрацию, чтобы оставить только те строки, где тег есть:

SELECT course_name
FROM courses
WHERE array_position(tags, 'Информатика') IS NOT NULL;

Результат:

Функция array_position() позволяет быстро находить данные внутри массива, что делает её одной из ключевых для работы с массивами в PostgreSQL.

Практическое применение функций

• unnest() — использовать для преобразования массивов в строки. Это важно для анализа данных, создания отчетов и работы с тегами.
• array_length() — идеально подходит для проверки длины массивов. Например, можно использовать её для валидации данных: проверять, чтобы массив не был пустым.
• array_position() — отличный инструмент для поиска элементов, будь то категории товаров, участие студента в проекте или ключевые слова в описании.

Типичные ошибки при использовании функций

1. unnest() может удвоить количество строк, если применяется к массивам сразу в нескольких столбцах. Это легко решить, добавив JOIN LATERAL или CROSS JOIN, но будьте внимательны.
2. array_length() возвращает NULL для пустого массива. Если ваш массив может быть пустым, проверяйте это отдельно.
3. array_position() может вернуть NULL, если элемент отсутствует. Поэтому лучше всегда учитывать этот случай в фильтрах (IS NOT NULL).

Примеры из реальной практики

Массивы в PostgreSQL — не просто теоретическая штука, а инструмент, который отлично работает в реальных проектах. Например, вы ведёте блог и у каждой статьи есть список тегов — массив позволяет легко фильтровать статьи по нужной теме или даже составлять топ популярных категорий.

Или, допустим, вы анализируете поведение пользователей, которые выбирают несколько интересующих курсов или продуктов. Все эти предпочтения удобно хранить в массиве — и так же удобно обрабатывать.

А ещё массивы помогают валидировать данные: с помощью array_length() можно, скажем, ограничить количество элементов — например, чтобы пользователь не выбирал больше пяти пунктов.

2

Задача

SQL SELF, 35 уровень, 2 лекция

Недоступна

Распаковка массива с `unnest()`

Распаковка массива с `unnest()`

Открыть