How can I update records based on conditions on nested fields?

user_1280443 0
Viewed 43

I have a bigquery table with the following schema:

{
  "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"
    }
  ]
}

As you can see the products field is a nested one. What I would like to achieve is an update condition like the following:

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;

And whenever I try to do so, I get the following error

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

I know that to SELECT stuff with the same logic, I can use the UNNEST statement, I am not able to apply this to UPDATE.

google-bigquery
1 Answers

Try EXISTS:

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