bigquery legacy sql POSITION() function in standard sql

Question

I try to translate a legacy query into a standard SQL query in bigquery, but I don't find the function position() in standard SQL.

Answer 1

You may be looking for the bracket operator. For example,

SELECT array_column[OFFSET(0)]
FROM dataset.table

This selects the first element of an array column for each row. If you want to flatten an array and get the offset of each element, you can do so like this:

SELECT x, x_offset
FROM dataset.table,
  UNNEST(array_column) AS x WITH OFFSET x_offset

See also the working with arrays documentation.