How do I update a nested record in BigQuery using DML syntax?

Question

I've got the following BigQuery schema, and I'm trying to update the event_dim.date field:

I tried the following query using standard SQL and the new BigQuery DML:

UPDATE `sara-bigquery.examples.app_events_20170113`
SET event_dim.date = '20170113'
WHERE true

But got this error:

Error: Cannot access field date on a value with type ARRAY<STRUCT<name STRING, params ARRAY<STRUCT<key STRING, 
value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>>, timestamp_micros INT64, ...>> at [2:15]

I'm able to select the nested field with this query:

 SELECT x.date FROM `sara-bigquery.examples.app_events_20170113`,
 UNNEST(event_dim) x

But can't figure out the correct UPDATE syntax.

Answer 1

That query failed because event_dim is an array of structs. This should do the trick:

UPDATE `sara-bigquery.examples.app_events_20170113`
SET event_dim = ARRAY(
  SELECT AS STRUCT * REPLACE('20170113' AS date) FROM UNNEST(event_dim)
)
WHERE true

Check out the docs on how arrays are handled in Standard SQL for more details.