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

sql google-bigquery ansi-sql
hhh picture hhh · Apr 7, 2018 · Viewed 12k times · Source

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?

Answer

Gordon Linoff picture Gordon Linoff · Apr 7, 2018

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

Related questions

efficient way to compare two tables in bigquery

I am interested in comparing, whether two tables contain the same data. I could do it like this: #standardSQL SELECT key1, key2 FROM ( SELECT table1.key1, table1.key2, table1.column1 - table2.column1 as col1, table1.col2 - table2.col2 …

Read More
Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>

I'm trying to run a query using Standard SQL Dialect (ie not Legacy SQL) on BigQuery. My query is: SELECT date, hits.referer FROM `refresh.ga_sessions_xxxxxx*` LIMIT 1000 But keep getting the error Error: Cannot access field referer on …

Read More
BigQuery SQL WHERE Date Between Current Date and -15 Days

I am trying to code the following condition in the WHERE clause of SQL in BigQuery, but I am having difficulty with the syntax, specifically date math: WHERE date_column between current_date() and current_date() - 15 days This seems …

Read More