Открыть сервис
Главная → Блог → UNNEST

UNNEST

UNNEST — это оператор языка SQL, предназначенный для преобразования массивов или коллекций в набор строк (отношение). Он относится к классу табличных функций и используется для развёртывания (декомпозиции) элементов массива в отдельные записи результирующего набора данных. В стандарте SQL/Foundation (ISO/IEC 9075) оператор определён как часть расширения для работы с массивами, однако его реализация и синтаксис различаются в зависимости от конкретной системы управления базами данных (СУБД). Основное назначение UNNEST — упрощение обработки многозначных атрибутов, хранящихся в виде массивов, без необходимости написания рекурсивных или итеративных процедур.

История

Концепция развёртывания массивов в реляционной модели возникла в 1990-х годах в связи с развитием объектно-реляционных СУБД. Первой коммерческой системой, реализовавшей UNNEST, стала PostgreSQL (версия 8.4, 2009 год), где оператор был введён как часть поддержки типов данных ARRAY. Впоследствии он был включён в стандарт SQL:2003, а затем уточнён в SQL:2008 и SQL:2016. В других СУБД — Oracle, IBM Db2, Google BigQuery, Amazon Redshift — появились аналогичные функции (например, TABLE(FLATTEN(...)) в Oracle или UNNEST в BigQuery). В Microsoft SQL Server и MySQL до версии 8.0 прямое использование UNNEST отсутствовало, вместо него применялись обходные пути — оператор APPLY или функции STRING_SPLIT и JSON_TABLE.

Синтаксис и поведение

Базовый синтаксис UNNEST в SQL (на примере PostgreSQL):

``sql SELECT unnest(array_column) AS element FROM table_name; ``

При выполнении оператор принимает на вход массив любого типа (числовой, строковый, дата, JSON и т.д.) и возвращает одну строку для каждого элемента массива. Порядок строк соответствует порядку элементов в исходном массиве. Если массив содержит NULL-элементы, они также включаются в результат. При передаче нескольких массивов одновременно (например, UNNEST(arr1, arr2)) строки формируются поэлементно: первый элемент первого массива объединяется с первым элементом второго, второй — со вторым и т.д. Если массивы имеют разную длину, результат дополняется NULL-значениями для более коротких массивов.

Примеры

  1. Простое развёртывание массива:

``sql SELECT unnest(ARRAY[1, 2, 3]) AS number; ` Результат: ` number ------- 1 2 3 ``

  1. Развёртывание с сохранением контекста строки:

``sql SELECT id, unnest(tags) AS tag FROM articles; ` Для таблицы articles с полем tags типа TEXT[]` каждая строка исходной таблицы порождает столько строк, сколько элементов в массиве.

  1. Множественное развёртывание:

``sql SELECT unnest(ARRAY['a','b','c']) AS letter, unnest(ARRAY[1,2,3]) AS number; ` Результат: ` letter | number -------+-------- a | 1 b | 2 c | 3 ``

Применение

UNNEST широко используется в аналитической обработке данных (OLAP), при работе с логами, временными рядами, JSON-документами и другими структурами, где данные хранятся в виде массивов. Основные сценарии:

  • Преобразование вложенных структур в плоские таблицы: для последующей агрегации, фильтрации или соединения с другими таблицами.
  • Обработка тегов и категорий: в системах управления контентом (CMS) и интернет-магазинах, где у одной записи может быть несколько тегов.
  • Работа с JSON-массивами: в PostgreSQL и BigQuery UNNEST применяется совместно с функциями json_array_elements или json_extract для извлечения элементов из JSON-документов.
  • Генерация последовательностей: с помощью UNNEST(ARRAY[...]) можно создавать тестовые наборы данных без использования циклов.
  • Загрузка данных из внешних источников: при импорте CSV-файлов с многозначными полями (например, список идентификаторов, разделённых запятыми) после предварительной конвертации в массив.

Особенности реализации в различных СУБД

PostgreSQL

Реализация UNNEST в PostgreSQL наиболее полная. Оператор поддерживает:

  • Развёртывание массивов любой размерности (многомерные массивы преобразуются в одномерные последовательности).
  • Использование с предложением WITH ORDINALITY для получения порядкового номера элемента.
  • Работу с пользовательскими типами данных и составными массивами.
  • Встроенную оптимизацию — при развёртывании массива, хранящегося в столбце таблицы, планировщик может использовать параллельное выполнение.

Google BigQuery

В BigQuery UNNEST является ключевым оператором для работы с повторяющимися полями (REPEATED). Синтаксис требует использования CROSS JOIN UNNEST(array_column). BigQuery автоматически поддерживает развёртывание вложенных структур (STRUCT) внутри массивов.

Amazon Redshift

В Redshift UNNEST реализован через функцию UNNEST в составе SELECT с LEFT JOIN. Оператор поддерживает только одномерные массивы и не может быть использован в подзапросах.

Oracle Database

В Oracle аналогом является оператор TABLE(FLATTEN(...)) или LATERAL VIEW с UNNEST (начиная с версии 12c). Для работы с коллекциями требуется предварительное объявление типа коллекции.

Microsoft SQL Server

Прямого оператора UNNEST нет. Вместо него применяется CROSS APPLY с табличной функцией STRING_SPLIT (для строк) или OPENJSON (для JSON-массивов). Начиная с SQL Server 2022, добавлена функция GENERATE_SERIES для генерации последовательностей, но она не является полным аналогом.

Производительность и ограничения

Использование UNNEST может приводить к значительному увеличению числа строк в результирующем наборе, что влияет на производительность запросов. В PostgreSQL для больших массивов рекомендуется использовать индексы по типу GIN (Generalized Inverted Index) для ускорения поиска по элементам массива. В BigQuery стоимость запроса прямо пропорциональна объёму обрабатываемых данных, поэтому развёртывание массивов с миллионами элементов может быть дорогостоящим.

Основные ограничения:

  • UNNEST не может быть использован в предложении WHERE для фильтрации по элементам массива без предварительного развёртывания.
  • В некоторых СУБД (например, MySQL) поддержка массивов отсутствует, что делает UNNEST неприменимым.
  • При развёртывании многомерных массивов порядок элементов может быть неочевиден: в PostgreSQL развёртывание происходит в порядке возрастания индексов по каждому измерению.

Альтернативы

В случаях, когда UNNEST недоступен или неэффективен, используются следующие подходы:

  • Рекурсивные общие табличные выражения (CTE): для поэлементного извлечения из массива с помощью рекурсии.
  • Функции STRING_SPLIT (SQL Server) или SPLIT_PART (PostgreSQL): для строковых представлений массивов.
  • Генерация последовательностей: с помощью GENERATE_SERIES (PostgreSQL, SQL Server 2022) и последующего индексирования массива.
  • Внешние функции: написание пользовательских функций на PL/pgSQL, Python или C для развёртывания массивов.

См. также

Примечания

  • В стандарте SQL:2016 UNNEST определён как часть раздела «Array-returning functions».
  • В некоторых документациях UNNEST классифицируется как табличная функция (table function), а не оператор.
  • Оператор UNNEST не следует путать с функцией unnest в языке Python (библиотека NumPy), которая выполняет сжатие многомерных массивов.

Источники

  • ISO/IEC 9075-2:2016, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
  • PostgreSQL Documentation: «Array Functions and Operators» (версия 16).
  • Google BigQuery Documentation: «Working with Arrays».
  • Amazon Redshift Documentation: «UNNEST function».
  • Oracle Database SQL Language Reference, 12c Release 2.
  • Microsoft SQL Server Documentation: «STRING_SPLIT (Transact-SQL)».

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →

Похожие материалы