使用另一个嵌套字段作为条件来更新BigQuery中的嵌套字段 [英] Update a nested field in BigQuery using another nested field as a condition

问题描述

我正在尝试在ga_sessions_表上更新sourcePropertyDisplayName，因为它与另一个嵌套字段的值匹配.我在这里找到了这个答案:

I am trying to update the sourcePropertyDisplayName on a ga_sessions_ table WHERE it matches the value of another nested field. I found this answer here:

更新BigQuery表中的嵌套字段

但这只有一个非常简单的WHERE TRUE；而我只想在匹配指定的hits.eventInfo.eventCategory时应用它.

But this only has a very simple WHERE TRUE; whereas I only want to apply it if it matches a specified hits.eventInfo.eventCategory.

这是我到目前为止所拥有的:

Here is what I have so far:

UPDATE `dataset_name`.`ga_sessions_20170720`
SET hits =
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT sourcePropertyInfo.* REPLACE('updated text' AS 
       sourcePropertyDisplayName)) AS sourcePropertyInfo)
    FROM UNNEST(hits)
  )
WHERE ARRAY(
SELECT AS STRUCT eventInfo.eventCategory
FROM UNNEST(hits)
) LIKE '%SEARCH%'

但是我当前遇到以下错误:

But I'm currently getting following error:

Error: No matching signature for operator LIKE for argument types: 
ARRAY<STRUCT<eventCategory STRING>>, STRING. Supported signatures: STRING 
LIKE STRING; BYTES LIKE BYTES at [8:7]

如何在WHERE子句中使用另一个嵌套字段的值来更新它?

How can I update one nested field by using the value of another in a WHERE clause?

推荐答案

您的WHERE子句应如下所示

Your WHERE clause should be like below

WHERE EXISTS (
  SELECT 1 FROM UNNEST(hits) AS h 
  WHERE h.eventInfo.eventCategory LIKE '%SEARCH%'
)

这篇关于使用另一个嵌套字段作为条件来更新BigQuery中的嵌套字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！