CODEWITHSUNDEEP
sqlgoogle-bigquery
MFR
MFR

Reputation: 2077

"contains" in Bigquery standard SQL

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]

Upvotes: 13

Views: 53310

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11777

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.

Upvotes: 20

Related Questions