Search code examples
sqlgoogle-bigqueryansi-sql

BigQuery Standard SQL: how to return the first value of array?

Small working example

SELECT SPLIT("hello::hej::hallo::hoi", "::")

returns an array [hello, hej, hallo, hoi] where I want to select the first element i.e. hello. BG Standard provides no FIRST, instead FIRST_VALUE(..) OVER() which I cannot get working for this example above, so

How can I select the first value of array with BigQuery Standard SQL?

Solution

  • I think the documentation in BigQuery is pretty good. You can read about arrays here.

    You can use either OFFSET() or ORDINAL(). The method would be:

    select array[offset(0)]

    or

    select array[ordinal(1)]