How to Update a field in a nested table?

[Tuuli]

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?

[Poala_Tenorio]

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.