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
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too