Menu
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]
964
Previously, BigQuery executed queries using a non-standard SQL dialect known as BigQuery SQL. With the launch of BigQuery 2.0, BigQuery released support for standard SQL, and renamed BigQuery SQL to legacy SQL. Standard SQL is the preferred SQL dialect for querying data stored in BigQuery.
BigQuery RegExp: How to replace special characters To replace special characters, you can use regular expressions like this [^a-zA-Z0-9]+ and REGEXP_REPLACE function.
The WITH clause contains one or more common table expressions (CTEs). Each CTE binds the results of a subquery to a table name, which can be used elsewhere in the same query expression. BigQuery does not materialize the results of non-recursive CTEs within the WITH clause.
Using SPLIT(value[, delimiter]) returns an array. Then using SAFE_OFFSET(zero_based_offset) or SAFE_ORDINAL(one_based_offset) to get item from array. SELECT SPLIT(app_info.
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.
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
