BigQuery using GoogleSQL.

Trying to MERGE data from staging table into existing table. I have a STRUCT of counts that need to be updated but I can’t figure out how to get into the STRUCT to first apply a condition and then update the values. My query below gives the error: Cannot access field type on a value with type ARRAY<STRUCT<type STRING, count INT64, amount INT64>> at [25:43].

MERGE
  caches.table1 old
USING
  (
  SELECT
    id,
    ARRAY_AGG(STRUCT(type,
        count,
        amount)) AS normalized_counts
  FROM (
    SELECT
      id,
      type,
      COUNT(*) AS count,
      SUM(amount) AS amount
    FROM
      production.table2
    GROUP BY
      id,
      type)
  GROUP BY
    id) staging
ON
  old.id = staging.id
  WHEN MATCHED AND (old.normalized_counts.type = staging.normalized_counts.type) THEN UPDATE SET normalized_counts.count = old.normalized_counts.count + staging.normalized_counts.count, normalized_counts.amount = old.normalized_counts.amount + staging.normalized_counts.amount
  WHEN NOT MATCHED
  THEN
INSERT
  (id,
    field2,
    normalized_counts,
    field3,
    field4,
    field5)
VALUES
  (id, NULL, normalized_counts, NULL, NULL, NULL)

I’ve tried UNNEST(), which works, but then I don’t know how to put it back into its original schema inside the UPDATE part. So right now I have 3 steps:

Step 1: Create a table with the unnested normalized_counts field from the existing table.

Step 2: Merge rows from staging into the existing table.

Step 3: Aggregate into normalized_counts STRUCT again, and join to rest of existing table, overwrite existing table with this.

Is it possible to get them all into one MERGE query? My best attempt was the query above.