如何使用UPDATE语句更新BigQuery中的重复字段元素

问题场景 #

现有BigQuery表ott_details，表结构及数据如下：

id  |  platforms  |  country
101    Netflix       UK
       Prime         US
       peacock       US

其中platforms和country为重复字段（Repeated Field），需要将platforms字段中的peacock替换为hotstar，更新后表结构如下：

id  |  platforms  |  country
101    Netflix       UK
       Prime         US
       hotstar       US

解决方案 #

使用BigQuery的数组处理函数结合UPDATE语句实现替换：

UPDATE `your-project-id.your-dataset.ott_details`
SET platforms = ARRAY(
  SELECT CASE 
           WHEN platform = 'peacock' THEN 'hotstar'
           ELSE platform
         END
  FROM UNNEST(platforms) AS platform
)
WHERE EXISTS (
  SELECT 1 FROM UNNEST(platforms) AS platform
  WHERE platform = 'peacock'
);

语句说明 #

• UNNEST(platforms)：将重复字段展开为单行数据
• CASE WHEN：匹配目标值并执行替换逻辑
• ARRAY(...)：将处理后的单行数据重新组合为数组格式
• WHERE EXISTS：仅对包含peacock的行执行更新，减少不必要的计算开销

内容的提问来源于stack exchange，提问作者Saravana Kumar