how to update BigQuery table using struct column

Question

My Big Query table T1 is as below.

T1

A string,
B string,
C Record,
C.key string,
C.formula string,
D string

I want to update column D based on B and C, with query something like below.

update T1
set D = 'd1'
where B = 'b1' and C.formula = 'f1' ;

How to do that in BigQuery ?

Answer 1

Even simpler version

update T1 t
set D = 'd1'
where B = 'b1' and
'f1' in (select formula from t.C);

Answer 2

You don't have a record for C. You have an array of records. So, you can use unnest():

update T1
    set D = 'd1'
    where B = 'b1' and
          exists (select 1
                  from unnest(T1.C) as c_el
                  where c_el.formula = 'f1'
                 );