Update Struct in BigQuery

Question

I was playing around on bigquery. I want to update a nested fields but I got this error:

Value of type ARRAY<STRUCT<STRUCT<STRING, STRING, STRING>>> cannot be assigned to click_url, which has type STRUCT<string STRING, text STRING, provided STRING> at [2:17]

Table:

Here's the code. I've tried this one since I saw some thread suggesting to use UNNEST function but the click_url is not an array it's a struct.

UPDATE `myTable` t
SET click_url =  
  ARRAY(
    SELECT AS STRUCT(c.string, c.text, c.provided) FROM UNNEST([t.click_url]) as c
  )
WHERE id = 10

Answer 1

Looking at the error message closely, the target column type is a STRUCT without Array

 STRUCT<string STRING, text STRING, provided STRING>

hereby as you hinted, the UNNEST is not necessary. try to update the STRUCT directly.

UPDATE `myTable` t
SET click_url =  
    (SELECT AS STRUCT(
      t.click_url.string, 
      t.click_url.text, 
      your_hard_coded_provided_string)
    )

WHERE id = 10