Google BigQuery - Updating a nested repeated field

1
votes

I am trying to update the hits.page.pagePath field in the Google Analytics export in BigQuery, however i'm unable to unnest the field using the method outlined in the documentation. In my case, the pagePath field is nested at two levels, "hits" and "page".

https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#update_repeated_records

The following code is incomplete, but I'm stuck with unnesting pagePath from the page RECORD.

UPDATE `project-name.datasetId.ga_sessions_yyyymmdd`

SET hits = ARRAY( 
  SELECT page.pagePath FROM UNNEST(hits) as pagePath
  )

WHERE fullVisitorID like "%1%"

Has anyone managed to use update this field or a similar one in the GA export?

Thanks.

2
google-bigquery
Can't quite comprehend what you are trying to do here. Why you want to update the hits of ga_session tables? Something wrong happened in the export? You want to update it with the same hits the fullvisitor had in his session? – Willian Fuks

2 Answers

0
votes

This should help get you started. You need to include everything else from hits in order to perform the update, including the nested page.

UPDATE `you_dataset.tablename`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    (SELECT AS STRUCT page.* REPLACE ('foo' AS pagePath)) AS page
  )
  FROM UNNEST(hits) as pagePath
)
WHERE fullVisitorID like "%1%"
2
votes

Below is for BigQuery Standard SQL 

UPDATE `project-name.datasetId.ga_sessions_yyyymmdd`
SET hits =  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT * 
        REPLACE('Your New pagePath' AS pagePath) 
        FROM UNNEST([page])
      ) AS page) 
    FROM UNNEST(hits)
  ) 
WHERE fullVisitorID like "%1%"

As you can see in above example you would replace pagePath with string 'Your New pagePath'
Of course in reality you would want to have some logic here - so replace that part to whatever logic you need - like for example you would need to UPPER whole string - you would use something like below 

        REPLACE(UPPER(pagePath) AS pagePath)