复杂类型 · R2 SQL docs

R2 SQL 支持查询 Iceberg 表中存储的 struct、array 和 map 列类型。本页介绍了每种类型的访问模式、支持的函数和示例。

结构体 (Structs)

结构体列包含命名字段。使用括号表示法或 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 创建一个带有命名字段的结构体
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 创建一个带有位置字段的结构体
SELECT struct(customer_id, total_amount, region) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

数组 (Arrays)

数组列包含有序的值列表。数组索引是从 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 1

string_to_array

通过分隔符将字符串拆分为数组。

SELECT string_to_array(categories, ',') AS cat_array
FROM my_namespace.products
WHERE categories IS NOT NULL
LIMIT 5

range

生成一个从 start（包含）到 stop（排除）的整数数组。

SELECT range(0, 5) AS nums
FROM my_namespace.sales_data
LIMIT 1

generate_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 5

cardinality

返回数组中的元素总数。array_length 的别名。

SELECT customer_id, cardinality(tags) AS tag_count
FROM my_namespace.products
LIMIT 5

empty

如果数组没有元素，则返回真。

SELECT customer_id, empty(tags) AS has_no_tags
FROM my_namespace.products
LIMIT 5

array_ndims

返回数组的维数。

SELECT array_ndims(make_array(1, 2, 3)) AS ndims
FROM my_namespace.sales_data
LIMIT 1

array_dims

返回数组的维度信息。

SELECT array_dims(make_array(1, 2, 3)) AS dims
FROM my_namespace.sales_data
LIMIT 1

搜索数组

array_has

如果数组包含某个值，则返回真。

SELECT customer_id, array_has(tags, 'premium') AS is_premium
FROM my_namespace.products
LIMIT 5

array_has_all

如果第一个数组包含第二个数组的所有元素，则返回真。

SELECT array_has_all(make_array(1, 2, 3, 4), make_array(2, 3)) AS has_all
FROM my_namespace.sales_data
LIMIT 1

array_has_any

如果第一个数组包含第二个数组的任何元素，则返回真。

SELECT array_has_any(make_array(1, 2, 3), make_array(3, 4, 5)) AS has_any
FROM my_namespace.sales_data
LIMIT 1

array_position

返回值的首次出现的位置（从 1 开始）。如果未找到则返回 0。

SELECT array_position(make_array('a', 'b', 'c', 'b'), 'b') AS pos
FROM my_namespace.sales_data
LIMIT 1

array_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 1

array_reverse

反转数组元素的顺序。

SELECT array_reverse(make_array(1, 2, 3)) AS reversed
FROM my_namespace.sales_data
LIMIT 1

array_distinct

从数组中删除重复的元素。

SELECT array_distinct(make_array(1, 2, 2, 3, 3, 3)) AS unique_vals
FROM my_namespace.sales_data
LIMIT 1

flatten

将嵌套数组展平一层。

SELECT flatten(make_array(make_array(1, 2), make_array(3, 4))) AS flat
FROM my_namespace.sales_data
LIMIT 1

array_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 1

array_prepend

将值添加到数组的开头。

SELECT array_prepend(0, make_array(1, 2, 3)) AS prepended
FROM my_namespace.sales_data
LIMIT 1

array_concat

连接两个或多个数组。

SELECT array_concat(make_array(1, 2), make_array(3, 4)) AS merged
FROM my_namespace.sales_data
LIMIT 1

array_remove

从数组中删除首次出现的值。

SELECT array_remove(make_array(1, 2, 3, 2), 2) AS result
FROM my_namespace.sales_data
LIMIT 1

array_remove_all

从数组中删除所有出现的值。

SELECT array_remove_all(make_array(1, 2, 3, 2, 2), 2) AS result
FROM my_namespace.sales_data
LIMIT 1

array_remove_n

从数组中删除前 n 次出现的值。

SELECT array_remove_n(make_array(1, 2, 2, 2, 3), 2, 2) AS result
FROM my_namespace.sales_data
LIMIT 1

array_replace

替换数组中首次出现的值。

SELECT array_replace(make_array(1, 2, 3), 2, 99) AS result
FROM my_namespace.sales_data
LIMIT 1

array_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 1

array_replace_all

替换数组中所有出现的值。

SELECT array_replace_all(make_array(1, 2, 3, 2), 2, 99) AS result
FROM my_namespace.sales_data
LIMIT 1

array_pop_back

从数组中删除最后一个元素。

SELECT array_pop_back(make_array(1, 2, 3)) AS result
FROM my_namespace.sales_data
LIMIT 1

array_pop_front

从数组中删除第一个元素。

SELECT array_pop_front(make_array(1, 2, 3)) AS result
FROM my_namespace.sales_data
LIMIT 1

array_repeat

将一个值重复指定的次数作为数组。

SELECT array_repeat(region, 3) AS repeated
FROM my_namespace.sales_data
LIMIT 1

array_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 1

array_union

返回两个数组中的所有唯一元素。

SELECT array_union(make_array(1, 2, 3), make_array(3, 4, 5)) AS merged
FROM my_namespace.sales_data
LIMIT 1

array_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 5

array_min

返回数组中的最小值。

SELECT customer_id, array_min(scores) AS min_score
FROM my_namespace.products
LIMIT 5

array_any_value

返回数组中的第一个非 NULL 值。

SELECT array_any_value(make_array(NULL, 42, NULL)) AS first_val
FROM my_namespace.sales_data
LIMIT 1

array_element

返回给定索引处的元素（从 1 开始）。等效于括号表示法访问 (arr[idx])。

SELECT array_element(make_array(10, 20, 30), 2) AS second_val
FROM my_namespace.sales_data
LIMIT 1

转换数组 (Convert arrays)

array_to_string

使用分隔符将数组元素连接成一个字符串。

SELECT customer_id, array_to_string(tags, ', ') AS tag_list
FROM my_namespace.products
LIMIT 5

映射 (Maps)

映射列存储键值对。使用 map_keys、map_values 和 map_extract 来查询它们。

map_keys

以数组形式返回映射中的所有键。

SELECT map_keys(metadata) AS keys
FROM my_namespace.products
LIMIT 5

map_values

以数组形式返回映射中的所有值。

SELECT map_values(metadata) AS vals
FROM my_namespace.products
LIMIT 5

map_extract

返回特定键的值。

SELECT map_extract(metadata, 'source') AS source,
       map_extract(metadata, 'store_name') AS store
FROM my_namespace.products
LIMIT 5

内联创建映射

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(keys_arr, vals_arr)`	从键和值数组创建映射
`map_keys(map)`	以数组形式的所有键
`map_values(map)`	以数组形式的所有值
`map_extract(map, key)`	特定键的值