Update ARRAY IN ARRAY BigQuery (GA4 Data)

Question

I want to update a value in array with a value in another table.

table1:

event_params.key	event_params.value.string_value
country	US

table2:

country	new_country
US	NL

I try

UPDATE table1

SET event_params = ARRAY(SELECT AS STRUCT * REPLACE ( new_country AS value.string_value ) FROM UNNEST(event_params) 
WHERE key = "country")

FROM(SELECT country, new_country FROM table2)

WHERE 
(SELECT value.string_value from unnest(event_params) where key = 'country') = t2.country

It doesnt work.. The problem is the value.string_value because is also an array.

The final table should look like table1:

event_params.key	event_params.value.string_value
country	NL

Now I want to update table1 with table2 to udate the country from US to NL in table1.

How can I take the array in array?

fsdjlcsd · Accepted Answer · 2022-11-24 14:18:35Z

0

I also saw this code:

UPDATE `project.dataset.your_table` t
SET hits = 
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      ARRAY(
        SELECT AS STRUCT product.* REPLACE(
          CASE WHEN map.raw_name = product.productCategory THEN category 
            ELSE productCategoryAttribute END AS productCategoryAttribute)
        FROM UNNEST(product) product
        LEFT JOIN UNNEST(agg_map.map) map 
        ON map.raw_name = product.productCategory
      ) AS product)
    FROM UNNEST(hits) hit
  ) 
FROM (SELECT ARRAY_AGG(row) map FROM `project.dataset.map` row) agg_map 
WHERE TRUE

BigQuery UPDATE nested array field But I cant implement it.

answered Nov 24, 2022 at 14:18

fsdjlcsd

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update ARRAY IN ARRAY BigQuery (GA4 Data)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related