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
WITH items AS (
  SELECT ["apples", "bananas", "pears", "grapes"] AS list UNION ALL
  SELECT ["coffee", "tea", "milk" ] AS list UNION ALL
  SELECT ["cake", "pie"] AS list
)
SELECT 
  list AS original_list, 
  ARRAY(SELECT item FROM UNNEST(list) item WITH OFFSET pos WHERE pos < 2) new_list
FROM items

with output as

Row original_list   new_list     
1   apples          apples   
    bananas         bananas  
    pears        
    grapes       
2   coffee          coffee   
    tea             tea  
    milk         
3   cake            cake     
    pie             pie

Mike-Barn

posted on 31 Dec 18