Unnest first N items in a Google BigQuery Repeated Field

Is there a way to select the first n items in a Google BigQuery Repeated field?

Below is for BigQuery Standard SQL

#standardSQL\nWITH items AS (\n  SELECT ["apples", "bananas", "pears", "grapes"] AS list UNION ALL\n  SELECT ["coffee", "tea", "milk" ] AS list UNION ALL\n  SELECT ["cake", "pie"] AS list\n)\nSELECT \n  list AS original_list, \n  ARRAY(SELECT item FROM UNNEST(list) item WITH OFFSET pos WHERE pos < 2) new_list\nFROM items\n

with output as

Row original_list   new_list     \n1   apples          apples   \n    bananas         bananas  \n    pears        \n    grapes       \n2   coffee          coffee   \n    tea             tea  \n    milk         \n3   cake            cake     \n    pie             pie\n

Mike-Barn

posted on