I am trying to perform what, you would think, is a trivial operation in BigQuery; I am trying to update a nested field in a BigQuery table that is the result of a 360 export.
Here is my query:
#standardSQL
UPDATE `dataset_name`.`ga_sessions_20170705`
SET hits.eventInfo.eventLabel = 'some string'
WHERE TRUE
But I get this error message:
Error: Cannot access field eventInfo on a value with type ARRAY<STRUCT<item STRUCT<transactionId INT64, currencyCode STRING>, isEntrance BOOL, minute INT64, ...>> at [3:10]
How can I update this nested field?
hits
is an array, so you need to use an array subquery to assign to it. It would look something like this:
#standardSQL
UPDATE `dataset_name`.`ga_sessions_20170705`
SET hits =
ARRAY(
SELECT AS STRUCT * REPLACE(
(SELECT AS STRUCT eventInfo.* REPLACE('some string' AS eventLabel)) AS eventInfo)
FROM UNNEST(hits)
)
WHERE TRUE;
If you need to modify a given custom dimension you can use this:
#standardSQL
UPDATE `tablename`
SET hits =
ARRAY(
SELECT AS STRUCT * REPLACE(
ARRAY(
SELECT AS STRUCT cd.index,
CASE WHEN cd.index = index_number THEN 'new value'
ELSE cd.value
END
FROM UNNEST(customDimensions) AS cd
) AS customDimensions)
FROM UNNEST(hits) hit
)
WHERE TRUE
But it takes a while to run.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With