Search code examples
sqlstructgoogle-bigquerynested

Updating a struct column in bigquery

I have a table with 1 normal mode column id and a struct(record, repeated) column details. The details column has country and name column nested inside that.

I have five records inside the details column.

country name
UK      sarah
IND     carter
Itay    marie
France  john
peru    christy

I am using the below query to update the value of a single record.

update 
info_table
set value=array(
    select as struct * replace('US' as country)) from unnest(details)
    where name='sarah'
)
where id=100

This updates the country value from UK to US successfully, however, this does remove the rest of the 4 records.

I need a query to update the struct fields without messing up the other records.

Solution

  • Try the following :

    UPDATE info_table
SET details = (
  SELECT ARRAY(
    SELECT AS STRUCT
      IF(name = 'sarah', 'US', country) AS country,
      name
    FROM UNNEST(details)
  )
)
WHERE id = 100