Google BigQuery - 更新嵌套的重复字段 [英] Google BigQuery - Updating a nested repeated field

问题描述

我正在尝试更新 BigQuery 中 Google Analytics 导出中的 hits.page.pagePath 字段，但是我无法使用文档中概述的方法取消嵌套该字段.在我的例子中，pagePath 字段嵌套在两个级别，hits"和page".

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

以下代码不完整，但我坚持从页面记录中取消嵌套 pagePath.

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%"

是否有人设法在 GA 导出中使用更新此字段或类似字段?

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

谢谢.

推荐答案

这应该可以帮助您入门.您需要包含 hits 中的所有其他内容才能执行更新，包括嵌套的 page.

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%"

这篇关于Google BigQuery - 更新嵌套的重复字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！