"contains" in Bigquery standard SQL

10
votes

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]

1
sqlgoogle-bigquery

1 Answers

16
votes

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.