Update a nested field in BigQuery using another nested field as a condition

Question

I am trying to update the sourcePropertyDisplayName on a ga_sessions_ table WHERE it matches the value of another nested field. I found this answer here:

Update nested field in BigQuery table

But this only has a very simple WHERE TRUE; whereas I only want to apply it if it matches a specified hits.eventInfo.eventCategory.

Here is what I have so far:

UPDATE `dataset_name`.`ga_sessions_20170720`
SET hits =
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT sourcePropertyInfo.* REPLACE('updated text' AS 
       sourcePropertyDisplayName)) AS sourcePropertyInfo)
    FROM UNNEST(hits)
  )
WHERE ARRAY(
SELECT AS STRUCT eventInfo.eventCategory
FROM UNNEST(hits)
) LIKE '%SEARCH%'

But I'm currently getting following error:

Error: No matching signature for operator LIKE for argument types: 
ARRAY<STRUCT<eventCategory STRING>>, STRING. Supported signatures: STRING 
LIKE STRING; BYTES LIKE BYTES at [8:7]

How can I update one nested field by using the value of another in a WHERE clause?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-07-21T15:56:36

Your WHERE clause should be like below

WHERE EXISTS (
  SELECT 1 FROM UNNEST(hits) AS h 
  WHERE h.eventInfo.eventCategory LIKE '%SEARCH%'
)

Update a nested field in BigQuery using another nested field as a condition

1 Answers