更新BigQuery表中的嵌套字段 [英] Update nested field in BigQuery table

问题描述

我试图在BigQuery中执行一些简单的操作，我试图更新BigQuery表中的嵌套字段，这是360导出的结果。

以下是我的查询：

  #standardSQL 
 UPDATE`dataset_name`.`ga_sessions_20170705` 
 SET hits.eventInfo.eventLabel ='some string'
 WHERE TRUE 
  

但是我收到以下错误消息：

错误：无法在类型为ARRAY< STRUCT< item STRUCT< transactionId INT64，currencyCode STRING> ;, isEntrance BOOL，minute INT64，...>>的值上访问字段eventInfo。在[3:10]

如何更新此嵌套字段？

code> hits 是一个数组，因此您需要使用数组子查询来指定它。它看起来像这样：

pre $
UPDATE`dataset_name` hits =
ARRAY（
SELECT AS STRUCT * REPLACE（
（SELECT AS STRUCT eventInfo。* REPLACE（'some string'AS eventLabel））AS eventInfo）
FROM UNNEST（hits ）
）
WHERE TRUE;

I am trying to perform what, you would think, is a trivial operation in BigQuery; I am trying to update a nested field in a BigQuery table that is the result of a 360 export.

Here is my query:

#standardSQL
UPDATE `dataset_name`.`ga_sessions_20170705`
SET hits.eventInfo.eventLabel = 'some string'
WHERE TRUE

But I get this error message:

Error: Cannot access field eventInfo on a value with type ARRAY<STRUCT<item STRUCT<transactionId INT64, currencyCode STRING>, isEntrance BOOL, minute INT64, ...>> at [3:10]

How can I update this nested field?

解决方案

hits is an array, so you need to use an array subquery to assign to it. It would look something like this:

#standardSQL
UPDATE `dataset_name`.`ga_sessions_20170705`
SET hits =
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT eventInfo.* REPLACE('some string' AS eventLabel)) AS eventInfo)
    FROM UNNEST(hits)
  )
WHERE TRUE;

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