在 BigQuery 中更新结构数组中的值 [英] Update values in struct arrays in BigQuery

问题描述

我正在寻找一种简单的方法来使用 SQL 更新结构数组中的值.假设我们有一张桌子:

I am looking for an easy way to update values inside array of structs using SQL. Let's say we have a table:

CREATE TABLE schema.table
(
    date DATE,
    weights ARRAY<STRUCT<animal STRING, value FLOAT64>>
)
;

insert into schema.table
select cast('2020-01-01' as date), [('dog', 10.2), ('bird', 0.7), ('dragon', 3.2)]
union all
select cast('2020-01-02' as date), [('dog', 10.3), ('bird', 0.7)]
union all
select cast('2020-01-03' as date), [('dragon', 3.3)]

所以表格看起来像:

我想以某种方式更新此表并将所有 dragon 名称更改为 cat.

I would somehow like to update this table and change all dragon names to a cat.

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard SQL

#standardSQL
UPDATE `project.dataset.table` t
SET weights =  
  ARRAY(
    SELECT AS STRUCT IF(animal = 'dragon', 'cat', animal) animal, value
    FROM t.weights 
  ) 
WHERE TRUE

这篇关于在 BigQuery 中更新结构数组中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！