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?

Garay

Answer 1

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

Appetizer

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

Answer 2

If you have NULL values

  select array[safe_offset(0)]

Saidee