← R2 SQL / r2-sql / sql-reference
Сложные типы
R2 SQL поддерживает запросы к столбцам типов struct, array и map, хранящимся в таблицах Iceberg. На этой странице описаны способы доступа, поддерживаемые функции и примеры для каждого типа.
Структуры
Столбцы-структуры содержат именованные поля. Доступ к полям можно получить с помощью нотации в квадратных скобках или get_field() функцию.
Нотация с квадратными скобками
SELECT pricing['price'] AS price,
pricing['discount_percent'] AS discount
FROM my_namespace.products
LIMIT 5функция get_field
SELECT get_field(pricing, 'price') AS price,
get_field(pricing, 'discount_percent') AS discount
FROM my_namespace.products
LIMIT 5Поля структур в WHERE
SELECT customer_id, pricing['price'] AS price
FROM my_namespace.products
WHERE pricing['price'] > 50
LIMIT 10Поля структур в ORDER BY
SELECT customer_id, pricing['price'] AS price
FROM my_namespace.products
WHERE pricing['price'] IS NOT NULL
ORDER BY pricing['price'] DESC
LIMIT 10Поля структур в GROUP BY
SELECT platforms['windows'] AS windows_support,
COUNT(*) AS product_count,
AVG(pricing['price']) AS avg_price
FROM my_namespace.products
WHERE pricing['price'] IS NOT NULL
GROUP BY platforms['windows']Встроенное создание структур
-- named_struct creates a struct with named fields
SELECT named_struct('id', customer_id, 'amount', total_amount) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1
-- struct creates a struct with positional fields
SELECT struct(customer_id, total_amount, region) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1Массивы
Столбцы типа массив содержат упорядоченные списки значений. Индексация массива с отсчётом от 1.
Доступ по индексу
SELECT customer_id, tags[1] AS first_tag, tags[2] AS second_tag
FROM my_namespace.products
LIMIT 5Создание массивов
make_array
Создаёт массив из списка значений.
SELECT make_array(1, 2, 3) AS nums
FROM my_namespace.sales_data
LIMIT 1string_to_array
Разбивает строку на массив по разделителю.
SELECT string_to_array(categories, ',') AS cat_array
FROM my_namespace.products
WHERE categories IS NOT NULL
LIMIT 5диапазон
Генерирует массив целых чисел от start (включительно) до stop (не включая).
SELECT range(0, 5) AS nums
FROM my_namespace.sales_data
LIMIT 1generate_series
Генерирует массив целых чисел от start до stop включительно.
SELECT generate_series(1, 5) AS nums
FROM my_namespace.sales_data
LIMIT 1Просмотр массивов
array_length
Возвращает количество элементов массива.
SELECT customer_id, array_length(tags) AS tag_count
FROM my_namespace.products
LIMIT 5cardinality
Возвращает общее количество элементов массива. Псевдоним для array_length.
SELECT customer_id, cardinality(tags) AS tag_count
FROM my_namespace.products
LIMIT 5пусто
Возвращает true, если массив не содержит элементов.
SELECT customer_id, empty(tags) AS has_no_tags
FROM my_namespace.products
LIMIT 5array_ndims
Возвращает количество измерений массива.
SELECT array_ndims(make_array(1, 2, 3)) AS ndims
FROM my_namespace.sales_data
LIMIT 1array_dims
Возвращает размерности массива.
SELECT array_dims(make_array(1, 2, 3)) AS dims
FROM my_namespace.sales_data
LIMIT 1Поиск в массивах
array_has
Возвращает true, если массив содержит значение.
SELECT customer_id, array_has(tags, 'premium') AS is_premium
FROM my_namespace.products
LIMIT 5array_has_all
Возвращает true, если первый массив содержит все элементы второго.
SELECT array_has_all(make_array(1, 2, 3, 4), make_array(2, 3)) AS has_all
FROM my_namespace.sales_data
LIMIT 1array_has_any
Возвращает true, если первый массив содержит хотя бы один элемент второго.
SELECT array_has_any(make_array(1, 2, 3), make_array(3, 4, 5)) AS has_any
FROM my_namespace.sales_data
LIMIT 1array_position
Возвращает позицию первого вхождения значения (нумерация с 1). Если значение не найдено, возвращает 0.
SELECT array_position(make_array('a', 'b', 'c', 'b'), 'b') AS pos
FROM my_namespace.sales_data
LIMIT 1array_positions
Возвращает все позиции значения в виде массива.
SELECT array_positions(make_array(1, 2, 1, 3, 1), 1) AS positions
FROM my_namespace.sales_data
LIMIT 1Преобразование массивов
array_sort
Сортирует элементы массива.
SELECT array_sort(make_array(3, 1, 2)) AS sorted
FROM my_namespace.sales_data
LIMIT 1array_reverse
Меняет порядок элементов массива на обратный.
SELECT array_reverse(make_array(1, 2, 3)) AS reversed
FROM my_namespace.sales_data
LIMIT 1array_distinct
Удаляет повторяющиеся элементы массива.
SELECT array_distinct(make_array(1, 2, 2, 3, 3, 3)) AS unique_vals
FROM my_namespace.sales_data
LIMIT 1flatten
Разворачивает вложенный массив на один уровень.
SELECT flatten(make_array(make_array(1, 2), make_array(3, 4))) AS flat
FROM my_namespace.sales_data
LIMIT 1array_slice
Возвращает срез массива от начального индекса до конечного (оба включительно, индексация с 1).
SELECT array_slice(make_array(10, 20, 30, 40, 50), 2, 4) AS sliced
FROM my_namespace.sales_data
LIMIT 1Изменение массивов
array_append
Добавляет значение в конец массива.
SELECT array_append(make_array(1, 2, 3), 4) AS appended
FROM my_namespace.sales_data
LIMIT 1array_prepend
Добавляет значение в начало массива.
SELECT array_prepend(0, make_array(1, 2, 3)) AS prepended
FROM my_namespace.sales_data
LIMIT 1array_concat
Объединяет два или более массива.
SELECT array_concat(make_array(1, 2), make_array(3, 4)) AS merged
FROM my_namespace.sales_data
LIMIT 1array_remove
Удаляет первое вхождение значения из массива.
SELECT array_remove(make_array(1, 2, 3, 2), 2) AS result
FROM my_namespace.sales_data
LIMIT 1array_remove_all
Удаляет все вхождения значения из массива.
SELECT array_remove_all(make_array(1, 2, 3, 2, 2), 2) AS result
FROM my_namespace.sales_data
LIMIT 1array_remove_n
Удаляет первые n вхождений значения из массива.
SELECT array_remove_n(make_array(1, 2, 2, 2, 3), 2, 2) AS result
FROM my_namespace.sales_data
LIMIT 1array_replace
Заменяет первое вхождение значения в массиве.
SELECT array_replace(make_array(1, 2, 3), 2, 99) AS result
FROM my_namespace.sales_data
LIMIT 1array_replace_n
Заменяет первые n вхождений значения в массиве.
SELECT array_replace_n(make_array(1, 2, 2, 2, 3), 2, 99, 2) AS result
FROM my_namespace.sales_data
LIMIT 1array_replace_all
Заменяет все вхождения значения в массиве.
SELECT array_replace_all(make_array(1, 2, 3, 2), 2, 99) AS result
FROM my_namespace.sales_data
LIMIT 1array_pop_back
Удаляет последний элемент массива.
SELECT array_pop_back(make_array(1, 2, 3)) AS result
FROM my_namespace.sales_data
LIMIT 1array_pop_front
Удаляет первый элемент массива.
SELECT array_pop_front(make_array(1, 2, 3)) AS result
FROM my_namespace.sales_data
LIMIT 1array_repeat
Повторяет значение заданное количество раз, формируя массив.
SELECT array_repeat(region, 3) AS repeated
FROM my_namespace.sales_data
LIMIT 1array_resize
Изменяет размер массива до заданной длины, заполняя пустые позиции значением по умолчанию.
SELECT array_resize(make_array(1, 2), 5, 0) AS resized
FROM my_namespace.sales_data
LIMIT 1Операции над множествами для массивов
array_intersect
Возвращает элементы, общие для обоих массивов.
SELECT array_intersect(make_array(1, 2, 3), make_array(2, 3, 4)) AS common
FROM my_namespace.sales_data
LIMIT 1array_union
Возвращает все уникальные элементы из обоих массивов.
SELECT array_union(make_array(1, 2, 3), make_array(3, 4, 5)) AS merged
FROM my_namespace.sales_data
LIMIT 1array_except
Возвращает элементы первого массива, отсутствующие во втором.
SELECT array_except(make_array(1, 2, 3, 4), make_array(2, 4)) AS diff
FROM my_namespace.sales_data
LIMIT 1Агрегация значений массива
array_max
Возвращает максимальное значение в массиве.
SELECT customer_id, array_max(scores) AS max_score
FROM my_namespace.products
LIMIT 5array_min
Возвращает минимальное значение в массиве.
SELECT customer_id, array_min(scores) AS min_score
FROM my_namespace.products
LIMIT 5array_any_value
Возвращает первое значение массива, отличное от NULL.
SELECT array_any_value(make_array(NULL, 42, NULL)) AS first_val
FROM my_namespace.sales_data
LIMIT 1array_element
Возвращает элемент по заданному индексу (нумерация с 1). Эквивалентно обращению через квадратные скобки (arr[idx]).
SELECT array_element(make_array(10, 20, 30), 2) AS second_val
FROM my_namespace.sales_data
LIMIT 1Преобразование массивов
array_to_string
Объединяет элементы массива в строку с разделителем.
SELECT customer_id, array_to_string(tags, ', ') AS tag_list
FROM my_namespace.products
LIMIT 5Карты
Столбцы Map хранят пары ключ-значение. Используйте map_keys, map_values, а также map_extract чтобы их запрашивать.
map_keys
Возвращает все ключи из map в виде массива.
SELECT map_keys(metadata) AS keys
FROM my_namespace.products
LIMIT 5map_values
Возвращает все значения из map в виде массива.
SELECT map_values(metadata) AS vals
FROM my_namespace.products
LIMIT 5map_extract
Возвращает значение для указанного ключа.
SELECT map_extract(metadata, 'source') AS source,
map_extract(metadata, 'store_name') AS store
FROM my_namespace.products
LIMIT 5Встроенное создание map
SELECT map(make_array('a', 'b'), make_array(1, 2)) AS m
FROM my_namespace.sales_data
LIMIT 1Полный указатель функций
Функции для структур
| Функция | Описание |
|---|---|
struct_col['field'] |
Доступ к полю через нотацию с квадратными скобками |
get_field(struct, 'field') |
Доступ к полю через функцию |
named_struct(k1, v1, ...) |
Создание структуры с именованными полями |
struct(v1, v2, ...) |
Создание структуры с позиционными полями |
Функции для работы с массивами
| Функция | Описание |
|---|---|
make_array(v1, v2, ...) |
Создание массива из значений |
string_to_array(str, delim) |
Разбиение строки на массив |
range(start, stop) |
Генерация диапазона целых чисел (stop не включается) |
generate_series(start, stop) |
Генерация последовательности целых чисел (stop включается) |
array_length(arr) |
Количество элементов |
cardinality(arr) |
Количество элементов |
empty(arr) |
Истина, если пусто |
array_ndims(arr) |
Количество измерений |
array_dims(arr) |
Информация о размерности |
array_has(arr, val) |
Проверка вхождения |
array_has_all(arr, arr2) |
Проверка вхождения всех элементов |
array_has_any(arr, arr2) |
Проверка вхождения любого элемента |
array_position(arr, val) |
Первая позиция значения |
array_positions(arr, val) |
Все позиции значения |
array_sort(arr) |
Сортировка элементов |
array_reverse(arr) |
Обратный порядок |
array_distinct(arr) |
Удалить дубликаты |
flatten(arr) |
Разворачивание на один уровень |
array_slice(arr, start, end) |
Извлечение подмассива |
array_append(arr, val) |
Добавить в конец |
array_prepend(val, arr) |
Добавление в начало |
array_concat(arr1, arr2) |
Объединение массивов |
array_remove(arr, val) |
Удалить первое вхождение |
array_remove_all(arr, val) |
Удалить все вхождения |
array_remove_n(arr, val, n) |
Удалить первые n вхождений |
array_replace(arr, old, new) |
Заменить первое вхождение |
array_replace_n(arr, old, new, n) |
Заменить первое n вхождений |
array_replace_all(arr, old, new) |
Заменить все вхождения |
array_pop_back(arr) |
Удалить последний элемент |
array_pop_front(arr) |
Удалить первый элемент |
array_repeat(val, n) |
Повторить значение n раз |
array_resize(arr, size, default) |
Изменить размер с заполнением по умолчанию |
array_intersect(arr1, arr2) |
Общие элементы |
array_union(arr1, arr2) |
Объединение элементов |
array_except(arr1, arr2) |
Разность элементов |
array_max(arr) |
Максимальное значение |
array_min(arr) |
Минимальное значение |
array_any_value(arr) |
Первое значение, отличное от NULL |
array_to_string(arr, delim) |
Объединение элементов в строку |
array_element(arr, idx) |
Элемент по индексу |
Функции Map
| Функция | Описание |
|---|---|
map(keys_arr, vals_arr) |
Создание map из массивов ключей и значений |
map_keys(map) |
Все ключи в виде массива |
map_values(map) |
Все значения в виде массива |
map_extract(map, key) |
Значение для конкретного ключа |