sql - Update values in struct arrays in BigQuery

Question

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)]

So the table looks like:

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

Answer 1

Waitting for answers