google-bigquery - 如何根据嵌套字段的条件更新记录？

Question

我有一个具有以下架构的 bigquery 表：

{
  "fields": [
    {
      "name": "products",
      "type": "RECORD",
      "mode": "REPEATED",
      "fields": [
        {
          "name": "name",
          "type": "STRING",
          "mode": "REQUIRED"
        },
        {
          "name": "qty",
          "type": "INTEGER",
          "mode": "REQUIRED"
        },
        {
          "name": "variant_name",
          "type": "STRING",
          "mode": "REQUIRED"
        },
        {
          "name": "order_id",
          "type": "INTEGER",
          "mode": "REQUIRED"
        },
        {
          "name": "test_mode",
          "type": "BOOLEAN",
          "mode": "REQUIRED"
        },
        {
          "name": "amount",
          "type": "FLOAT",
          "mode": "REQUIRED"
        },
        {
          "name": "transaction_reference",
          "type": "STRING",
          "mode": "REQUIRED"
        },
        {
          "name": "id",
          "type": "INTEGER",
          "mode": "REQUIRED"
        },
        {
          "name": "source",
          "type": "STRING",
          "mode": "REQUIRED"
        },
        {
          "name": "currency",
          "type": "STRING",
          "mode": "REQUIRED"
        }
      ]
    },
    {
      "name": "processed_at",
      "type": "TIMESTAMP",
      "mode": "REQUIRED",
      "description": "bq-datetime"
    },
    {
      "name": "inserted_at",
      "type": "TIMESTAMP",
      "mode": "REQUIRED",
      "description": "bq-datetime"
    }
  ]
}

如您所见，该products字段是一个嵌套字段。我想要实现的是如下更新条件：

UPDATE `dataset.table`
SET processed_at = '2021-04-17T16:07:30.993806'
WHERE processed_at = '1970-01-01T00:00:00' AND products.order_id = 9366054;

每当我尝试这样做时，我都会收到以下错误

Cannot access field order_id on a value with type ARRAY<STRUCT<name STRING, qty INT64, variant_name STRING, ...>> 

我知道要SELECT使用相同的逻辑，我可以使用该UNNEST语句，但无法将其应用于UPDATE.

标签： google-bigquery

Answer 1

尝试存在：

WHERE processed_at = '1970-01-01T00:00:00'
  and EXISTS (SELECT *
              FROM UNNEST(products)
              WHERE order_id = 9366054
             );