Update nested record with where condition in Bigquery

1
votes

I have a bigquery table 'metrics' which has nested record fields One of the fields is called resource.labels which has key pair values as shown belowenter image description here

I need to the update resource.labels which is a repeated record field and has a key value pair resource.labels.key and resource.labels.value on this condition

where key="machinetype" and value="N/A" set value="g1.small"

I have tried this

update `metrics` set resource.labels.value=ARRAY(SELECT AS STRUCT * REPLACE("g1.small" as value) from UNNEST(resource.labels) as h where h.key="machinetype" and h.value="N/A") where resource.labels.key="machinetype" and resource.labels.value="N/A"

but gives this error:

Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRING>> at [1:241]

can anyone give any suggestions? Thanks.

1
google-bigquery

1 Answers

2
votes

Try below- should work

UPDATE `project.dataset.metrics`
SET resource = (
    SELECT AS STRUCT * REPLACE(
      ARRAY(
        SELECT AS STRUCT key, 
          IF(key='machinetype' and value='N/A', 'g1.small', value) as value
        FROM t.labels
      ) AS labels)
    FROM UNNEST([resource]) t
  )
WHERE true