Skip to content
Advertisement

How do you swap two attributes in array column on Bigquery?

I have a table with two columns: group_id, students (Array of student). Each student have a two attribute: name and last_name. How do you swap name with last_name for each student?

Students table:
group_id | students.name | students.last_name
---------------------------------------------
1        | Perez         | Juan
---------------------------------------------
2        | Caicedo       | Pedro

I need to swapping students.name to students.last_name, any idea?

Advertisement

Answer

Finally, thanks to Mikhail Berlyant who answered this question (Update values in struct arrays in BigQuery), i was able to exchange attributes on update clause.

UPDATE `Students` s
SET students =  
   ARRAY(
       SELECT 
         AS STRUCT 
           last_name, name
       FROM s.students
   ) 
WHERE TRUE;

Too, i was able to exchange attributes on update clause with if condition for example.

UPDATE `Students` s
SET students =  
   ARRAY(
       SELECT 
         AS STRUCT 
           IF(CONTAINS_SUBSTR(name, 'Pedro'), name, last_name), IF(CONTAINS_SUBSTR(last_name, 'Caicedo'), last_name, name)
       FROM s.students
   ) 
WHERE TRUE;

It’s very important the position in the STRUCT, in my case name is first and last_name is second, for achieve the exchange just by changing its position the objective is achieved.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement