使用Bigquery中的where条件更新嵌套记录 [英] Update nested record with where condition in Bigquery

问题描述

我有一个BigQuery表' metrics '，该表具有嵌套的记录字段，其中一个字段称为 resource.labels ，该表具有如下所示的密钥对值

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 below

我需要更新resource.labels，它是一个重复的记录字段，并且在这种情况下具有键值对resource.labels.key和resource.labels.value

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"

我已经尝试过了

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"

但出现此错误:

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

有人可以提出任何建议吗?谢谢.

can anyone give any suggestions? Thanks.

推荐答案

尝试以下操作-应该可以

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

这篇关于使用Bigquery中的where条件更新嵌套记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！