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

Question

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)]

Comments

[Gordon Linoff]

@JervisChionh . . . The answer is not appropriate for a comment. It requires unnesting and recreating the array.