我有一个数组列，我想获取它的第一个N元素（保持数组数据类型）。有一些不错的方法吗？理想情况下，无需取消嵌套，将array_agg排序并返回到数组。
我也可以这样做（获取前两个元素）：
WITH data AS
(
SELECT 1001 as id,['a','b','c'] as array_1
UNION ALL
SELECT 1002 as id,['d','e','f','g'] as array_1
UNION ALL
SELECT 1003 as id,['h','i'] as array_1
)
select *,[array_1[SAFE_OFFSET(0)],array_1[SAFE_OFFSET(1)]] as my_result
from data
但是显然，这不是一个好的解决方案，因为如果某个数组只有1个元素，它将失败。
这是带有UDF的常规解决方案，您可以调用任何数组类型：
CREATE TEMP FUNCTION TopN(arr ANY TYPE,n INT64) AS (
ARRAY(SELECT x FROM UNNEST(arr) AS x WITH OFFSET off WHERE off < n ORDER BY off)
);
WITH data AS
(
SELECT 1001 as id,['a','b','c'] as array_1
UNION ALL
SELECT 1002 as id,['d','e','f','g'] as array_1
UNION ALL
SELECT 1003 as id,['h','i'] as array_1
)
select *,TopN(array_1,2) AS my_result
from data
它使用了unnest和数组函数，听起来好像您不想使用它，但是它具有足够通用的优点，可以将任何数组传递给它。
,BigQuery Standard SQL（带有JS UDF）的另一个选项
#standardSQL
CREATE TEMP FUNCTION FirstN(arr ARRAY<STRING>,N FLOAT64)
RETURNS ARRAY<STRING> LANGUAGE js AS """
return arr.slice(0,N);
""";
SELECT *,FirstN(array_1,3) AS my_result
FROM data