Multi tool use

Update nested field in BigQuery table

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]

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?

2 Answers
2

hits is an array, so you need to use an array subquery to assign to it. It would look something like this:

hits

#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;


ARRAY subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [4:3]

SELECT AS STRUCT

Error: Value of type ARRAY<STRUCT<item STRUCT<transactionId INT64, currencyCode STRING>, isEntrance BOOL, minute INT64, ...>> cannot be assigned to hits, which has type ARRAY<STRUCT<item STRUCT<transactionId INT64, currencyCode STRING>, isEntrance BOOL, minute INT64, ...>> at [4:3]

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.