CODEWITHSUNDEEP
google-bigquery
Avi L
Avi L

Reputation: 1708

Update BigQuery repeated record field with condition

I have a BigQuery table named 'events' with below structure:

Table structure

Data example: enter image description here

My goal is to update all rows where country is US and replace it with 'United States', and where country is CH to replace it with 'China'.

I tried to look at other questions where they update repeated record but I didn't find anything specific to what I want and didn't quite understood the method. (for example this StackOverflow question)

Any help would be much appreciated.

Upvotes: 0

Views: 494

Answers (1)

Jaytiger
Jaytiger

Reputation: 12254

If you're trying to modify only two country codes into country names, following would be one of possible approach.

Note - below query is assuming that country code and region code can conflict each other.

UPDATE sample
   SET attributes = ARRAY (
     SELECT AS STRUCT a.name, IFNULL(c.name, a.value)
       FROM UNNEST(attributes) a 
       LEFT JOIN UNNEST([
         STRUCT('US' AS code, 'United States' AS name), ('CH', 'China')
       ]) c ON a = ('country', c.code)
   )
 WHERE ('country', 'US') IN UNNEST(attributes)
    OR ('country', 'CH') IN UNNEST(attributes)
;

enter image description here

More general approach would be using a mapping table.

DECLARE countries ARRAY<STRUCT<code STRING, name STRING>> DEFAULT [
  ('US', 'United States'),
  ('CH', 'China') --, you can add more mappings of country code and it's name.
];

With the above mapping table, below query will generate same result as first query.

UPDATE sample
   SET attributes = ARRAY(
     SELECT AS STRUCT a.name, IFNULL(c.name, a.value)
       FROM UNNEST(attributes) a 
       LEFT JOIN UNNEST(countries) c ON a = ('country', c.code)
   )
 WHERE TRUE
;

Upvotes: 2

Related Questions