How to Update a field in a nested table?

Google Cloud Data Analytics
1

Hi all,

I’m currently trying to update a field in an universal analytics sessions table. The table is nested, so a simple update query is not working, I was also not able to update the field after unnesting the structure.

The table structure is documented here: https://support.google.com/analytics/answer/3437719?hl=en

More specifically, I need to update the field ‘hits.eventInfo.eventLabel’ , only the rows where ‘hits.eventInfo.eventAction’ and ‘hits.eventInfo.eventCategory’ have a certain value.

My query is the following:

UPDATE my_database.DataDel.ga_sessions_*
SET hits = ARRAY(
SELECT AS STRUCT * REPLACE(
(SELECT AS STRUCT
eventInfo.eventCategory,
eventInfo.eventAction,
CASE
WHEN eventInfo.eventLabel LIKE “%@%” THEN “removed”
ELSE eventInfo.eventLabel
END AS eventLabel,
eventInfo.eventValue
) AS eventInfo
FROM UNNEST(hits.eventInfo) AS eventInfo
WHERE eventInfo.eventAction = ‘abc_action’
AND eventInfo.eventCategory = ‘xyz_category’
)
AS eventInfo)
AS hits
WHERE TRUE;

Could anyone help me to refine this query or let me know if an update of the field ‘hits.eventInfo.eventLabel’ in a nested table is possible?

2

To update a nested field like hits.eventInfo.eventLabel in a Google Analytics session table, you’re on the right track with your query. However, there’s a slight refinement needed to properly structure the update:

UPDATE `my_database.DataDel.ga_sessions_*`
SET hits =
ARRAY(
SELECT AS STRUCT * REPLACE(
ARRAY(
SELECT AS STRUCT
eventCategory,
eventAction,
CASE
WHEN eventLabel LIKE "%@%" THEN "removed"
ELSE eventLabel
END AS eventLabel,
eventValue
FROM UNNEST(eventInfo)
) AS eventInfo
)
FROM UNNEST(hits)
WHERE EXISTS (
SELECT 1
FROM UNNEST(eventInfo)
WHERE eventAction = 'abc_action'
AND eventCategory = 'xyz_category'
)
)
WHERE TRUE;

In this corrected query:

  • The ARRAY function surrounds the whole subquery, indicating that you’re replacing the entire array of hits.
  • Inside the subquery, hits is unnested and then reconstructed with the updated eventInfo array.
  • The REPLACE function replaces the eventInfo array in each hit with the updated version.
  • The EXISTS clause is used to filter hits based on the conditions eventAction = ‘abc_action’ and eventCategory - ‘xyz_category’ within the nested eventInfo array.
  • This query should update the eventLabel field for hits meeting your specified conditions in the nested structure. Make sure to replace ‘abc_action’ and ‘xyz_category’ with your actual criteria. Also, double-check the syntax and adapt it to your specific database if needed.
1 Like