Biqquery Update only part of Pagepath

Question

I'm trying to replace part of a pagepath shown in BigQuery and keep everything else intact. I can extract the data fine, but having problems trying to write the 'Update command'. What I'd like to do looks like:

update ga_sessions_20180101
UNNEST(GA.hits) hits
set hits.page.pagePath = REGEXP_REPLACE(page, r'.*string_selected=([^\&]+)','New Value')
where CAST(visitID AS STRING) = "1234"

But of course it doesn't like the unnest statement after the update... Could you please help. Thank you

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-07-12T23:16:09

First I would recommend to polish you logic and prone it via just SELECT statement to make sure that your code works before you will apply the UPDATE statement

So, the proper SELECT would be as below

SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT * 
        REPLACE(REGEXP_REPLACE(pagePath, r'.*string_selected=([^\&]+)','New Value') AS pagePath) 
        FROM UNNEST([page])
      ) AS page) 
    FROM UNNEST(hits)
  ) AS hits)
FROM `project.dataset.ga_sessions_20180101`
WHERE CAST(visitID AS STRING) = "1234"

above leaves the whole schema untouched with exception of ONLY one field - pagePath!

Now, after you have your logic polished - you can proceed with UPDATE as below

UPDATE `project.dataset.ga_sessions_20180101`
SET hits =  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT * 
        REPLACE(REGEXP_REPLACE(pagePath, r'.*string_selected=([^\&]+)','New Value') AS pagePath) 
        FROM UNNEST([page])
      ) AS page) 
    FROM UNNEST(hits)
  ) 
WHERE CAST(visitID AS STRING) = "1234"

Assuming regexp and where clause is correct in your original question (as I have not changed it at all) and does what you want - above is what you asked for!

Biqquery Update only part of Pagepath

1 Answers