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

问题描述

我正在尝试更新BigQuery的Google Analytics(分析)导出中的hits.page.pagePath字段，但是我无法使用文档中概述的方法取消嵌套该字段.就我而言，pagePath字段嵌套在两个级别，即命中"和页面".

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

以下代码是不完整的，但是我无法从RECORD页面取消嵌套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屋！