时间戳

首页 > 解决方案 > 我需要更新 BigQuery 表中数组中的值

google-bigquery - 我需要更新 BigQuery 表中数组中的值

问题描述

我需要从嵌套字段（在数组中）中清理一些邮件地址。

我创建了一个正则表达式来识别地址并更新它们。这适用于数据不在数组中的表，但我无法让它适用于我要清理的数据在数组中的位置。

UPDATE `somedata`, UNNEST(hits) AS hits
set 
  page_url = if (regexp_contains(hits.url,"([a-zA-Z0-9._%-]+(?:@|%40)[a-zA-Z0-9._%-]+[.][a-zA-Z]{2,})"), regexp_replace(hits.url,"([a-zA-Z0-9._%-]+(?:@|%40)[a-zA-Z0-9._%-]+[.][a-zA-Z]{2,})","removed%40removed.com"), hits.url)
WHERE
       regexp_contains(hits.url,"([a-zA-Z0-9._%-]+(?:@|%40)[a-zA-Z0-9._%-]+[.][a-zA-Z]{2,})")

无法访问类型为 ARRAY> 的值的字段页面

标签： google-bigquery

解决方案

我无法让它适用于我要清理的数据位于数组中的位置。

不确定您的表的架构，因此下面的示例基于来自bigquery-public-data.google_analytics_sample公共数据集的 ga_sessions_ 表的架构，因为看起来您的架构可能相似，因此您可以轻松调整以适应您的确切用例 

UPDATE `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
SET hits =  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT * 
        REPLACE(REGEXP_REPLACE(pagePath,r'match_expression','replace_expression') AS pagePath) 
        FROM UNNEST([page])
      ) AS page) 
    FROM UNNEST(hits)
  )   
WHERE TRUE

推荐阅读