"contains" in Bigquery standard SQL

Question

I wish to migrate from Legacy SQL to Standard SQL

I had the following code in Legacy SQL

SELECT
  hits.page.pageTitle
FROM [mytable] 
WHERE hits.page.pageTitle contains '%' 

And I tried this in Standard SQL:

SELECT
  hits.page.pageTitle
FROM `mytable` 
WHERE STRPOS(hits.page.pageTitle, "%") 

But it gives me this error:

Error: Cannot access field page on a value with type ARRAY> at [4:21]

Answer 1

Try this one:

SELECT 
  hits.page.pageTitle
FROM `table`,
UNNEST(hits) hits
WHERE REGEXP_CONTAINS(hits.page.pageTitle, r'%')
LIMIT 1000

In ga_sessions schema, "hits" is an ARRAY (that is, REPEATED mode). You need to apply the UNNEST operation in order to work with arrays in BigQuery.