GA4 data in BigQuery: Update page_location field-CodePudding

I am trying to find a way how to update records in the BigQuery-Export of GA4 data. This is the corresponding field:

To get that field I am using following query:

select
 pageLocation
from
 (select
  (select value.string_value from unnest(event_params) where key = 'page_location') as pageLocation
 from `myTable`
)

My update statement currently looks like this:

update `myTable` t
set 
t.event_params = (
    select
      array_agg(
        struct(
            (select value.string_value from unnest(t.event_params) where key = 'page_location') = 'REDACTED'
        )
      )
    from
      unnest(t.event_params) as ep
  )
where
  true

But I am getting the error "Value of type ARRAY<STRUCT> cannot be assigned to t.event_params, which has type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ..."

So it looks like the whole array needs to be reconstructed, but as there are many different values for event_params.key this does not seem to be the best way. Is there are way to directly update the corresponding field with BigQuery?

CodePudding user response：

You might consider below:

CREATE TEMP TABLE `ga_events_20210131` AS 
SELECT * FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`;

UPDATE `ga_events_20210131` t
   SET event_params = ARRAY(
         SELECT AS STRUCT
           key,
           STRUCT (
             IF(key = 'page_location', 'REDACTED', value.string_value) AS string_value,
             value.int_value, value.float_value, value.double_value
           ) AS value
           FROM t.event_params
       )
 WHERE TRUE;

SELECT * FROM `ga_events_20210131` LIMIT 100;

Query results